Blog - Page 9 of 10 - Office PowerUps

04Apr 2013 by Charlie Nichols 1 Comment

How to enable the Analysis ToolPak Add-in in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Lots of cool analysis capabilities are available when you enable the Analysis ToolPak in Excel. Here are the few simple steps to enable the features. Just which set of steps you use will depend on which version of Excel you have. Excel 2007 steps to enable the Analysis ToolPak Click the Office Start button, then click Excel Options. Click on the Add-ins section in the left navigation. Make sure Excel Add-ins is showing in the Manage drop-down box, then click Go. The Add-in list will show up. Click the checkbox next to Analysis ToolPak. Click OK. The above will enable the Analysis TookPak in Excel 2007 for you. Excel 2010 steps to enable the Analysis ToolPak The steps for Excel 2010 are basically the same, but the windows have only a slightly different…

04Apr 2013 by Charlie Nichols No Comments

Simple explanation of the Excel LOOKUP function

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel has a number of functions that will look up a value based on finding a corresponding value in a range, row, or column. Meet LOOKUP The Excel LOOKUP function matches a value you're looking for in a row or column and then returns the value from another matching row or column. I tried to create a visual to help. Part 1 in the image is LOOKUP finding the value you're looking for (represented by the "?"). Part 2 is LOOKUP finding the corresponding value in the column or row you specify. So the value returned (shown in red above) is the value you were looking for based on the initial value you were matching. Note that in the picture above, both looking up by row and by…

04Apr 2013 by Charlie Nichols No Comments

How to find values in one set that are duplicated in another set in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Ok, you have a set of values in a column or a row and you want to find out if any of those values exist in another list (which could be in either a row or a column elsewhere). You can do that in Excel by using the MATCH function to test for a specific value in another range. We’ll look at that below. Worksheet setup First, let’s set up a worksheet example. You can replace with any values you like of course. Here’s a list we want to find duplicates from. Think of this as your ‘Test’ or source list. Red, Pink, Purple, Blue Here’s a list that we want to look into. Think of this as our ‘Master’ list. Red, Orange, Yellow, Green, Blue, Indigo, Violet…

04Apr 2013 by Charlie Nichols No Comments

How to change the case of your text in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Three functions to change the case of text in Excel You can easily change the case of text in Excel. Unfortunately, Excel doesn't support an in-place way of converting text from one case to another as you can do in Word, Excel's Office sibling. Out of the box, Excel provides a few worksheet functions that give you a basic level of conversion. But since they're functions, you can't change the text in place. You need to put the new value in another cell or use in another formula. Here are three text case conversion functions you can use. UPPER LOWER PROPER UPPER and LOWER do what you'd think. They convert to all upper or lower case text. PROPER will add an initial capital letter at the beginning of…

03Apr 2013 by Charlie Nichols No Comments

How you can use an add-in to make a quick-and-dirty sentiment analysis spreadsheet with Excel

400 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Note: This post describes some capability you only get by installing an add-in for Excel. The add-on used here is fully functional and free during its trial period - so you'd have plenty of time to try this out. The add-in is called Excel PowerUps Premium Suite and using the steps below you can create a little sentiment analysis tool using a feed from the Twitter API. A link to download the free trial is at the bottom of the page. Note2: The Twitter API used as an example in this post has been retired. The new API requires authentication as well as only returning JSON instead of XML. I'll be looking at adding both of these to the pwrWEBSERVICE and pwrFETCHXMLVIAGET functions in the weeks to come. When…

02Apr 2013 by Charlie Nichols 6 Comments

How to add a drop down list in Excel to your worksheet

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Two ways to add a drop down list in Excel Sometimes you have a sheet that you need other people to fill in. And, you have a column or range of cells that need to have values from a specific set of values. You don't want to have to deal with multiple variations of "yes", for example. You might wind up with "Yes", "1", "True", "Y", "T", and who knows what else. You need people to stick to the menu of choices to make your analytic life much easier. The solution: add a drop down list in Excel. You've seen drop down pick lists in other people's spreadsheets and want to do the same in yours. The process is pretty straightforward. I'll describe a couple of common ways below. The…

01Apr 2013 by Charlie Nichols No Comments

How to turn off the grid lines in Excel

100 level, Excel

Everything in Excel is laid out in that huge grid. Rows and columns. But if you don't want to see those grid lines Excel makes it really easy to turn them off. Just go to the View tab, and uncheck the Gridlines checkbox in the Show group. There you go.

29Mar 2013 by Charlie Nichols 2 Comments

How to refer to a cell in Excel and have that reference remain unchanged

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Do you remember playing Battleship (the board game). You called out the coordinates for your shot using letters for the row labels, and numbers for the column labels. Excel is laid out the same way (just reversed). When you reference a cell you use its column and row coordinates. For example, A1 represents the cell in the first column ("A") and the first row ("1"). When you reference that cell such as below you are using a relative reference. =A1 This is called relative because if you copy that formula to another cell, the reference will adjust to be relative to the original reference. For example, if you copy the formula above to the cell immediately below the formula will be changed to the one below automatically. You…

25Mar 2013 by Charlie Nichols No Comments

Keep your row and column headings visible as you scroll in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to freeze row and column headings in Excel It's pretty easy to get a large number of rows or columns in one of your spreadsheets. And often you wind up having to scroll up and down or side to side in order to keep track of which column or which row you are looking at. If you ever need to keep the row headings or the column headings "pinned" on the screen so that you can track your place easier you need to use the Split and Freeze Panes features in Excel. This will allow you to freeze row and column headings in Excel. PINNING THE COLUMN HEADINGS If you want to pin or lock in place your column headings Excel lets you do that with the…

22Mar 2013 by Charlie Nichols No Comments

Are you tired of looking at Excel formula errors?

200 level, Excel

Now that you're getting into using formulas in Excel you've found they can be quite useful. And by now you've found that sometimes they return error values. Sometimes, these can detect errors you need to fix. Other times, they just indicate that you don't yet have all the data populated in your worksheet. You wind up with a cell showing values such as #VALUE!, #DIV/0!, #N/A, etc. It just looks messed up and as if the whole sheet might be suspect. You can add another function to your cell that will "wrap" your existing function with a small layer of error protection. Meet IFERROR The IFERROR function lets you simply show one value if your function runs normally, and alternate value (that you define) if your function returns one of…