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
25Mar 2013 by Charlie Nichols No Comments
Keep your row and column headings visible as you scroll in Excel

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…
Read More
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…
Read More
21Mar 2013 by Charlie Nichols No Comments

Do you need your VLOOKUP to look to the left instead of the right?

300 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] I've used VLOOKUP for many many years. It's a straightforward enough concept: scan down a column for a match in a cell, then look over to the right in that row for the value we want to retrieve. And, all of this is wrapped into a single function call: VLOOKUP. But you may have found VLOOKUP has a particular limitation. It can only look to the right of the cell you've matched. If you've been able to plan ahead and lay out your data so that the column you're scanning is to the left of the range of information you're looking for, you're in luck I suppose. But if you're like me and your data is provided to you from another source or you don't always envision every…
Read More
20Mar 2013 by Charlie Nichols No Comments

Top 5 ways for you to get your extra tabs in Excel noticed

100 level, Excel
Have you ever shared an Excel workbook with information in additional tabs? And, later found out that over half the people didn't even notice there was any information in those tabs? I think that having an empty "Sheet2" and "Sheet3" tab by default has essentially trained us to ignore those tabs. It also doesn't help our cause by having those tabs at the bottom of the window, and they seem to nicely blend in amongst the noise down there, much like this toad. Here are five simple things you can try to make it more likely people will actually see and use the information in those extra tabs. After all, if you take the time to put information in them it would be good if people actually saw the information.…
Read More
20Feb 2013 by Charlie Nichols No Comments

How to set up a running total column in Excel

200 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Have you needed to set up a running total column in Excel? Perhaps you needed to maintain an account balance in the far right column of a worksheet. And then did you find yourself using one formula for the first row in the column, and then another formula to start adding the current row to the previous row? Well, as you know that method works. It's just a bit quirky looking -- and if you want to use that method in a table you aren't able to take advantage of the autofilling capabilities of the table. Here is a simpler way. It uses a mix of absolute and relative references on a range within the sum function. So, if you originally had a series of formulas that looked…
Read More
19Feb 2013 by Charlie Nichols No Comments

How you can “pin” the current date or time in Excel

200 level, Excel
Have you ever used the TODAY() or the NOW() function in Excel? They make it super easy to insert the current date and time into your worksheet. So long as you want the value to always be current those functions are great. If you open the worksheet tomorrow, the date or time information will reflect that moment in time. But, what if you wanted to put this moment in time into your worksheet -- and have that date or time be persistent. Or, what if you wanted to insert more of a timestamp than a dynamic date or time value? Here's a hot-key combination that makes that super easy too. Press CTRL+; (that's control and the semicolon) to insert the current date. Press CTRL+SHIFT+; (that's control, shift, and the semicolon)…
Read More