Top Excel Tips for Analysts

We’ve all been there: boss gives you an assignment such as making some useless excel template or to turn some bad numbers into favourable ones. Sometimes this involves a rather simple task, but a very repetitive or long process. The Excel tips below will greatly increase your efficiency as well as accuracy and help make a good impression on your supervisor.

I have left out some more advanced functionality such as advanced filtering, sorting, data validation, and pivot tables due to the fact that for some reason, these are more commonly known and Analysts are expected to know these features.

10. Paste Special –> Transpose

This is such a simple feature within Excel, but can save you some time and frustration. Just say you have column headings, but then you realize that they would be better used as row headings. Rather than copy and paste them all individually, select the range, Ctr-C (or edit –> copy for excel noobs), select start of desired new range, Paste Special –> Transpose. This will turn those X cells into Y cells in the exact same order. Pretty basic tool, yet somehow is forgotten.

9. Concatenate

Although this function is fairly well known, the full extent of this function is not as well known. To string together three separate cells into another cell, type in =CONCATENATE(A1, A2, A3). (replace the A1, A2, A3, etc, with whatever cells you want to string together). You can also substitute the “,” between strings with “&”.

In addition to this, you can throw in basically whatever you want between strings. For example,  =CONCATENATE(A1, ”   BLA  ”, A2, A3) , would result in “A1   BLA   A2A3″. Similarly, between commas, you can include other formulas such as an IF statement or a RIGHT function.

8. Protecting workbooks

A lot of people protect workbooks. However, the best part of protecting workbooks is that you can lock cells. In many cases, you have data somewhere in your spreadsheet that you want to maintain, or perhaps some cells with formulas you want to keep or maybe a list that needs to stay for data validation reasons. In these cases, you can Lock the cells so that nobody can edit them. This is useful if you provide somebody a document and only want them to enter information into given cells. To do this, select the cells you want to lock. Right click on them, go to format. Then go to the protection tab, and click the Locked checkbox. Then, go to the Review ribbon and protect the sheet.

7. WEEKDAY function

Have you ever needed to include all work days for a given range somewhere in your excel sheet? Rather than manually entering in dates, or even dragging and filling down days, use the following formula to display weekdays. Simply enter in a date in one cell, enter this formula in the following cell, then drag and fill. Just a tip though, you may need to use the format painter to copy the formatting of your start date and apply it to the WEEKDAY cell. Then proceed with the dragging.

=A1+IF(WEEKDAY(A1)=6,3,1) —> just change the “A1″ with whatever cell your start date is.

6. Remove Duplicates

Again, this is such a simple tool, and yet very few people know this. It is common in large spreadsheets that some records (ie a row) are duplicated. Rather than going through all of the rows to check for duplicates or even Ctr-F-ing (Edit –> find for noobs), you can simply select the rows you wish to search for duplicates, then click the Remove Duplicate button in the Data ribbon.

5. Conditional Formatting based on other cells

From my experience, most people are familiar with conditional formatting in general. For instance, for a given range, you want the cells to be green if the word “Complete” is in them. However, what is not as well known as that you can make format cells based on the contents of ANOTHER cell. So to expand the last example, just say you want the cells in Column B to go green if the cell next to it in Column A is “Complete”. This is possible by creating a new Conditional Formatting rule. Select “use a formula to determine which cells to format”. The formula to enter here is a bit different than just entering a formula in a cell.

For example: You want cell B2 to be green if cell A2 is “Complete”. In the conditional formatting formula, type this in: =A2=”Complete”. Then click on Format and choose the formatting options you want.

4. Text to Columns

This may be my favourite built in excel functionality. How many times do you have a string of text that you want to divide up? For instance, you have a cell that contains the first and last name of a person and you want to divide that into two cells, one for first name, and one for last name. Instance of using some inadequate and possibly confusing formula like right or left functions, use the Text to Columns feature in the Data ribbon. Click the button, follow the prompts, and tada!

Another example of this is if you have a identifier and a description in the same string, such as ID XX – Short Name. Using this tool, you can put the ID number in its own column as well as the short name. You’d also end up with “ID” and “-” in their own columns as well in order to select just the useable info, but then just simply delete those columns.

3. Name Manager

This is very useful if you are working with a big worksheet or reference the same range of cells frequently. Essentially, rather than including the range in your formula all the time (and trying to remember it or find them), just define the range in the Name Manager (in the Formula ribbon) and then just reference that name rather than range.

For example, say C2-C26 are your product names, instead of including C2:C26 in your formula all the time, after defining the name, you could just put PRODUCTS in your formula. This may seem silly now because “PRODUCTS” is longer than “C2:C26″ but this does actually same time.

3. COUNTIF

This is another classic one. I think it is becoming more popular now, but still is so incredibly useful. This function just counts based on a IF statement.

For example, you want to count all references of “Complete” in a range. Simply type in =COUNTIF(A1:A26,”Complete”) . This is great for some simple analysis. Similar to this, COUNTA counts all cells that are not blank in a given range. COUNTBLANK is the opposite of the aforementioned one.

1. Synchronous scrolling

The first step to this having two different book books that you want to have side by side so you can review or edit both. This is usually a nuisance to do manually. To do this, go to the View ribbon and click on View Side by Side. Then, once the two workbooks are arranged the way you wish, click on Synchronous Scrolling. This is great if you are comparing two identical documents and looking to make changes to one based on another. This way, when you scroll in one workbook, it will scroll in the other as well. This feature is very useful, avoids confusion, and ultimately increases productivity.

Share us by:

Follow us on:

Posted by Alex Dorward on Friday, January 20th, 2012. Filed under Real World, _Index. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry