05Apr 2013 by Charlie Nichols No Comments
How to turn on the Developer tab in Excel

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…
Read More
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…
Read More
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…
Read More
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…
Read More
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…
Read More
04Apr 2013 by Charlie Nichols No Comments
How to change the case of your text in Excel

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…
Read More
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…
Read More
02Apr 2013 by Charlie Nichols 6 Comments
How to add a drop down list in Excel to your worksheet

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…
Read More
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…
Read More