Today’s Snapshot Series article was written by member Daniel Schack. He outlines some incredibly useful formulas, shortcuts, and general tips about using Excel meaningfully.
Being able to manipulate and analyze data is a vital skill no matter what major you are, or what profession you are looking to go into. Having a solid understanding of Excel can go a long way to helping you stand out to your boss, especially if you can show them something they don’t know. While this article will not make you an expert (FIN 303 will help with that) it will teach you a few neat tricks that should help make your life easier with Excel.
When using Excel, there are three basic keys that you need to learn. I don’t mean basic in the sense that they enjoy pumpkin spice lattes, but rather in the sense that the majority of shortcuts utilize some combination of these three keys. These keys are Alt, Ctrl, and Shift. Although it may seem awkward or slow at the start, mastering these three keys will make you more efficient at Excel if you practice.
By holding down the Shift key, I am able to select multiple cells at once as shown below.
However, I can only select the cells one at a time. By holding down Ctrl+Shift at the same time, I can jump and select many cells. If I hit Ctrl+Shift, ↓ I will go from the above picture to this:
As you can see, my selection went as far down as the blank row of cells in row 12. This is how Ctrl works; it jumps to the furthest filled cell. Using Ctrl without Shift will not select the cells, but it will allow you to quickly navigate the spreadsheet without using your mouse.
Pressing the Alt key brings up the command keys for the ribbon at the top of the screen, as seen below.
Now by pressing any of the indicated keys on the keyboard I can quickly jump to that tab on the ribbon. In this example, I will press H for the Home tab, as that is the most commonly used tab, and it will clearly illustrate what happens next.
By pressing H I have brought up the keystroke options for the Home tab. I can now press any key here to modify the cell or spreadsheet that I am working on. For example, if I press 1, the selected cell (or cells) will be bolded. Some options like Border (B) have additional drop down menus. In these cases, you simply follow the keys until you obtain the formatting you want. So for example, if I wanted to add a bottom border I would use the keystrokes Alt, H, B, O. Try it out and see what happens (spoiler alert: it will add a bottom border to your selected cell)
Trace Precedents/Trace Dependents
One of the most frustrating things in Excel can be when you’re looking at a formula in a cell and you can’t figure out where the numbers are coming from or what numbers the cell modifies in the rest of the spreadsheet. Using the Trace Precedents command will show you where numbers are coming from, while Trace Dependents will show what other cells this cell is impacting.
Here you can see blue arrows which show that cells G9 and I30 are feeding into cell I9. When cells draw from multiple cells, using Trace Precedents makes it easier to track where the cell is pulling its data from. The keyboard shortcut to for Trace Precedents is Alt, M, P. M takes you to the Formulas tab, and P is the Trace Precedents command.
Trace Dependents is very similar to Trace Precedents, except it shows what the current cell feeds into. The shortcut for this command is Alt, M, D. What’s interesting is that if you hit the Trace Dependents command again, this happens.
It now shows the next step of cells that this cell feeds into. If you hit Trace Dependents enough times, you can see the entire tree of what the cell impact, like so.
Trace Precedents and Trace Dependents will also have arrows that seem to point to empty cells. When this happens, the cell is most likely drawing from, or feeding into cells in other sheets. So if you can’t figure out what is changing your cell on this sheet, it could be because it is pulling data from a different sheet. If you see this icon, , know that you should be looking at a different sheet for the data you want. Notice how the projected revenues feed into other sheets, which makes sense because revenue is what drives an organization.
If you want to move between different sheets without using the mouse (which you should), you can get there by pressing Ctrl+Page Up or Ctrl+Page Down. This will move you between sheets as shown below. I am now looking at the Balance Sheet for the company.
(Side note, to flip between the last window you were at and the current window on a PC, use Alt+tab. This is similar to the Ctrl+Page Up/Down function in Excel, except with windows. If you have a Mac go online and order a PC, then use the steps I just gave)
This spreadsheet seems to be missing some values. Instead of typing new formulas into each cell in the remaining projected years, I can highlight year 2011 by using Ctrl+Shift,↓ a few times to get my screen to look like this:
Now I let go of Ctrl, but I keep my finger on the Shift key and I move it over to the 2015 column so it looks like so:
Now that I have the cells that I want selected, I let go of Shift and press Ctrl, R. This is the fill right function. It will take the formulas I have put into 2011 and fill them to the right up to 2015. Then your sheet would look like this:
You have just saved yourself a lot of time by letting Excel do most of the work for you. You are happy, and your boss is happy. Good job.
Back on the income statement, we can see that the Growth Analysis numbers are given as percentages, but it looks sloppy. For starters, there are –s. Parentheses are better than – so we will change the formatting of these cells.
After selecting this group of cells by using Ctrl and Shift like we learned earlier we will let go and press Ctrl+1, which will bring up this dialogue box:
We already know that the cells are percentages, but we want parentheses instead of –s. There is no way to do this with the given options so we will write a custom number format (be excited, this is tricky and few people know how to do it).
First we will go to the Custom Number option by pressing tab and then ↓ a few times to get to Custom. Now we will write in the format.
First we will write the positive format. We want it to be a percentage so we will write “0.00%”. This tells Excel that we want 2 decimal places, and for these to be percentages. Next we will write the format for negative numbers. We start by inserting a ;. This separates positive and negative formats. Now we do the same as before, except we add () because these numbers will be negative, so we write “(0.00%)”.
Hit Enter and see your handiwork.
Well it works, but it looks sloppy. The decimals and % signs are not lined up because of the ) that the negative numbers have. We need to add a space the same size as the ) to the positive numbers to make the decimals line up nicely. To do this we hit Ctrl+1 again to bring up the dialogue box. Go to the 0.0% and add a “_)” to the end of it. So now your entire format should look like “0.00%_);(0.00%)” The “_)” tells Excel that you want a space the size of a ) at the end of each positive number. This will ensure that your positive and negative numbers line up on their decimals. Hit enter and see how it looks.
Now your Growth Analysis looks very sharp and professional. The last thing you want is for poor formatting to distract someone from your numbers, so taking the extra time to format everything nicely can go a long way.
You can do much more with custom number formatting than simply moving decimals. In the example below, the terms for these loans are in years. You cannot type the word years into the cell, or it will confuse Excel.
So how do you put the word years into the cell, but still have it able to do calculations? Good question. Open up the formatting dialogue box with Ctrl+1 and go to Custom.
Now in the Type box type: #,##0 “Years” as seen below.
The #s tell excel to only display a number if there is a value there, whereas a 0 tells excel to always display a number there. As an example if we wrote “0,000 “Years” instead of #,##0 “Years”, then Excel would display 0,030 Years, because we used 0s instead of #. This would look silly so we will not do it. But it is useful to know if you ever need to use this information in the future because you want Excel to display more digits.
Hit enter and you will see this:
Notice how the cell now says “30 years”, but the formula bar only sees the 30? This is what we want because since there are no words in the formula bar, I can include column B in the formulas that I will be using to calculate the various interest payments on these loans.
Now I need to copy the format of this cell to the others to do this I hit Ctrl+C to copy, and then I’ll let go of those keys and select that column with Ctrl+Shift,↓ as seen below.
Now instead of hitting Ctrl+V which is normal paste, I will do Ctrl+Alt+V which will bring up the paste special dialogue box. I do not want to just use normal paste because the bottom two terms are 20 years not 30 and if I do normal paste, it will replace them with 30. I only want to copy the formatting, so I will select the Formats option in the dialogue box. I can do this quickly by simply hitting “t” once the dialogue box is open because the letter t in Formats is underlined, indicating that t is the shortcut key for Formats. Hit enter.
Now all of the terms have the same formatting and can be used in formulas.
Paste special is very useful and can be used to copy values only, formats only, formulas, and other options. Take some time to familiarize yourself with the function, it’ll be worth it.
My favorite function that I learned over the summer at my internship was the CONCATENATE function. It was extremely useful for consolidating lines of database information into one cell for ease of reading. I have made a sample function below.You can see what it looks like in the cell as well as the formula bar:
As you can see, the function strings lines of text in with cell values to create cohesive sentences. The commas separate the cells and strings of texts. When inserting text it is important to remember “” so Excel knows what you are trying to say. Also note that the word “years” is not included in the text string because we added the word “years” in the number formatting. If we want it to say “years” we will need to add that part after B3 like so:
Although it can look odd in the formula bar, it is very useful if you ever need to print out large amounts of data that you want simplified into readable sentences. By Selecting cells I3-I10 with Shift, I can use Ctrl+D to quickly fill in the rest of the cells like so:
I learned this formula from talking to one of the accountants in the accounts payable department, which wasn’t even where I was working, over the summer. The moral here is to get to know as many people at your company as you can; you never know what neat tricks you will learn that you can apply to your own tasks.
Appearances matter. You wouldn’t hire someone who shows up to an interview in jeans and a t-shirt, so why would you turn in work that didn’t look its best? Take the time to understand how Excel works and how you can format the sheets to look their best. Your boss will notice the difference, especially if your work is compared to someone else’s.