Charlie Nichols, Author at Office PowerUps

05Apr 2013 by Charlie Nichols 1 Comment

Open xlsx files in Excel 2003

100 level, Excel

Do you still have Excel 2003, but somebody sent you a file from Excel 2007 or later? Need to open xlsx files in Excel 2003? Good news, you're not stuck - you're in luck. You can open xlsx files in Excel 2003 To open xlsx files in Excel 2003, Microsoft has an upgrade package called the Office Compatibility Pack that lets you open files in later versions of the Office apps. That's cool, because it saves you from having to upgrade the copy of Excel you have just to work with the new file formats. One thing you don't get is the new functions that might be used in the newer files. These would be worksheet functions that were added as part of the newer version of Excel. Here's a…

05Apr 2013 by Charlie Nichols No Comments

How to turn on the Developer tab in Excel

100 level, Excel

By now I'm guessing you've read some instructions that have asked you to click the Developer tab in Excel. But, you don't see it right? You need to turn on the Developer tab in Excel. Here are the few simple steps to enable the tab. There are steps for both Excel 2007 and 2010. Microsoft moved the option around a bit between versions. Steps to turn on the developer tab in Excel 2007 Click the Office Start button, then click Excel Options. Click on the Popular section in the left navigation, and then click the checkbox for Show Developer tab in the Ribbon. Click OK. The above will show the developer tab in Excel 2007 for you. Steps to turn on the developer tab in Excel 2010 Click File, then…

05Apr 2013 by Charlie Nichols No Comments

How you can use the Histogram tool in Excel

400 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] First off, what's a histogram? A histogram is just a chart that shows counts of items in discrete buckets. In other words, a bar chart. The histogram tool in Excel lets you define the groupings or buckets. Or, in the terms of the histogram you get to define the Bins. A standard bar chart in Excel will just chart the individual distinct values, which is often not what you need to do. Using the histogram tool you can have Excel automatically count the items in the various Bins you define, and then chart those. Note: You may have also heard of a Pareto chart. That's just a histogram sorted from highest count to lowest. Here's an illustration to highlight the difference Imagine you're a teacher and you need…

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.