Category Archives: Excel Tip of the Week

Excel Tip of the Week: Data Tables

Posted on February 17, 2012 by Doug Midkiff

Today’s topic is how to create a data table.

One of the things they teach us early in business school is to think broadly about our decisions. We are taught to think about best-case scenarios, worst-case scenarios, and everything in between. Excel can be very useful in exploring a number of different situations from a quantitative standpoint, and data tables are one of the main tools you can use to quickly and easily explore such scenarios.

Data tables allow you to see what the outcome would be if you changed one (or two) of your input variables. This means that instead of manually changing a cell one number at a time to see what would happen, you can list a large range of possible values for your input variable(s) and Excel will calculate the result for you.

Here is an example:

  1. Download the example spreadsheet here
  2. Make sure you are on the sheet titled “One Variable Data Table”
  3. Using the 8% discount rate, we can see that if service was expanded the NPV for 5 years would be $1,147 209. However, we are not sure if 8% is correct. We would like to see what the NPV would be if the discount rate was as low as 6%, as high as 10%, and everything in between.
Read the full post (est. reading time 2:04 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | Leave a comment

Excel Tip of the Week: Evaluate Formulas

Posted on February 10, 2012 by Doug Midkiff

Today’s topic is evaluating formulas.

Last week I covered the Show formula feature, and this week I’ll be covering a feature that helps even more with troubleshooting and understanding formulas.

Formulas in Excel can quickly spin out of control. Take for example this formula that I recently wrote:

IF($AF$37<>P17,0,IF(P56>0,P70*-$N$14,IF(P75<hurdle1,-P72,(FV(hurdle1,P17,,NPV(hurdle1,$N$74:O74)+$M$74)-(P46+(-P58)+(-P69))+($N$14*(P70-(FV(hurdle1,P17,,NPV(hurdle1,$N$74:O74)+$M$74)-(P46+(-P58)+(-P69)))/$N$9))))))

What a mess. I actually ended up finding a simpler solution, so I was able to do away with the above formula. However, before I was able to arrive at a better solution I was stuck working with a very complex formula. This is where Evaluate Formula really came in handy.

Even if you don’t think you’ll ever write something super complex in Excel, it is likely you will have to work in a spreadsheet with someone else’s tricky formulas. When that happens you’ll be glad you have Evaluate Formula in your Excel toolkit.

What the Evaluate Formula feature does is allow you to evaluate each part of a formula individually and see how Excel is calculating a specific piece of the formula. As usual, the best way to learn this is via an example: (Note to Mac users, this feature is not available in your version of Excel.)

Read the full post (est. reading time 1:34 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | Leave a comment

Excel Tip of the Week: Show Formulas

Posted on February 3, 2012 by Doug Midkiff

Today’s topic is showing formulas.

I’ll be honest, this tip isn’t SUPER useful. The biggest reason to know this tip is because at some point you (or maybe a friend who is going to ask you for help) will accidentally engage this feature. This can be scary if you don’t know what is happening because it changes the entire look of your spreadsheet. However, armed with the proper knowledge you can calmly disengage this feature and go back to making awesome spreadsheets.

Showing formulas is simply what it sounds like. Once you engage this feature, Excel will display the formulas within the cells instead of the formula results. For example, this:

Turns into this:

Notice how the “Total Sales” column has changed from numbers to formulas. Further, some of the other cells have lost their formatting. I didn’t show it here, but Excel also widens all the columns.

To turn this feature on/off go to the “Formulas” menu, then from the “Formula Auditing” section click on “Show Formulas” the keyboard shortcut is: Ctrl + ` (the key to the left of the 1). This shortcut works on both PC and Mac.

 

Permanent link to this post (est. reading time 46 secs)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , | 1 Comment

Excel Tip of the Week: Transpose

Posted on January 27, 2012 by Doug Midkiff

This week’s topic is transposing data.

Transposing is taking a set of data and reorienting it from vertical to horizontal or vice versa. When you are working with multiple rows or columns, transposing will essentially switch your column headings to row headings and vice versa. Excel has a few ways to accomplish this and I’ll be showing you two of them today. The first way is via the Paste Special feature (building on last week’s post) and the other way is through a special function called TRANSPOSE (go figure).

Paste Special: Transpose
This is the simpler method to transpose data, but as you’ll see it is static and doesn’t allow for dynamic updating. Here are the steps to transpose the data in the example spreadsheet.

  1. Download the example spreadsheet here.
  2. Highlight the range B3:M3 (all the months) and then copy (Ctrl +C)
  3. Move to cell C7
  4. PC: From the “Home” menu in the “Clipboard” section click “Paste” then select “Transpose.” The icon looks like this:  (Keyboard shortcut Alt → H → V → T)
  5. Mac: From the “Home” menu in the “Edit” section click “Paste” then select “Paste Special”. The “Transpose” option is in the bottom right of the dialogue box. Click OK.
Read the full post (est. reading time 1:44 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | Leave a comment

Excel Tip of the Week: Paste Special

Posted on January 20, 2012 by Doug Midkiff

This week’s topic is Paste Special.

I really should have covered this one a long time ago, but better late than never. I’ve actually hinted at this feature a couple times, most notably in the “Linked Picture” post, but also in the “Pasting into PowerPoint and Word” post.

This is one of the most essential features in Excel and allows you to do a number of things with your data. There is actually too much to cover in one post, so today I’ll be showing you 1) Paste Special: Values 2) Paste Special: Formats 3) Paste Special: Formulas.

All Paste Special options can be accessed by clicking on the “Home” menu and then in the “Clipboard” group clicking the “Paste” button (not the big clipboard icon). From there you can hover your mouse over the various icons to see what each one does.

For Mac users it is basically the same. After you click “Paste” you have to then click “Paste Special” and a dialog box will pop up showing you all your options.

Read the full post (est. reading time 1:39 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , | 1 Comment