Category Archives: Excel Tip of the Week

Excel Tip of the Week: Check Box

Posted on March 30, 2012 by Doug Midkiff

Today’s topic is how to create a check box.

Check boxes are technically an advanced Excel feature and somewhat hidden in Excel. But once you know how to create one, it is relatively simple to use.

Before we jump into creating check boxes, the first step is to enable the “Developer” tab in the Excel ribbon. By default this tab is hidden, and it is where you’ll find the check boxes among many other useful advanced Excel features. Here is how to enable the Developer Tab:

Windows:

  1. Click the File tab.
  2. Click Options.
  3. In the categories pane, click Customize Ribbon.
  4. In the list of main tabs, select Developer.
  5. Click OK to close the Options dialog box.

Mac:

  1. Click on the ‘Excel’ menu in the menu bar
  2. Click on Preferences
  3. Click on the Ribbon icon
  4. Select the Developer Tab
  5. Click OK

Check boxes give you the ability to toggle between two scenarios. For example, I’ve used them to toggle between a simple or detailed assumption. Or you could use it to turn things on and off. Let’s look at an example. First download this spreadsheet.

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

Excel Tip of the Week: Pivot Tables

Posted on March 23, 2012 by Doug Midkiff

This week’s topic is pivot tables.

Pivot tables are for organizing and summarizing large amounts of data in a quick and easy fashion. Anything you do with a pivot table can also be accomplished with formulas. However, pivot tables have a couple advantages over using formulas. First, it is quicker. Pivot tables allow users to drag-and-drop their information into a summary table without having to know a single formula. Second, pivot tables allow for greater flexibility. If your data is not arranged to your liking, then a few clicks of the mouse can completely rearrange the data. This is much more flexible than rewriting several formulas.

Let’s walk through an example that highlights some of the most basic pivot table features. Please download the example spreadsheet here.

When you open the spreadsheet you’ll notice that there is a large amount of data arranged in columns with column headers. This is how data needs to be arranged before using it in a pivot table. The information in the example spreadsheet is a list of over 2000 “green” cars along with information about those cars such as: fuel type, number of cylinders, air pollution score, etc. Follow these steps to create a pivot table for this data:

Read the full post (est. reading time 3:10 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | 2 Comments

Excel Tip of the Week: AND & OR

Posted on March 16, 2012 by Doug Midkiff

This week’s topic are the two logical functions AND and OR.

The past two week’s we’ve looked at the IF function and the IFERROR function. This post will finish up our “logical series” with two functions that aren’t very useful on their own, but can be very powerful when used within an IF function.

AND & OR are very similar. Essentially you feed these functions any number of logical tests and the function will return a TRUE or FALSE depending on the results of those tests.

When using the AND function it will return a TRUE if all of the tests are true. It will return a FALSE if any of the tests are false. Thus, you could have 10 tests that are true but if the 11th test is false then the result of the AND function will be FALSE.

The OR function works sort of opposite from the AND function. It will return TRUE if a single test is true. It will return a FALSE only if all of the tests are false. Thus, you could have 10 tests that are false, but if the 11th test is true then the result of the OR function will be TRUE.

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

Excel Tip of the Week: IFERROR

Posted on March 9, 2012 by Doug Midkiff

Today’s topic is the Excel function IFERROR.

We’re building off of last week’s IF post and looking at a similar function this week. IFERROR is new to Excel as of 2007, so I’d like to start by offering a word of warning when using this function. If there is a chance that you or someone else will have to use your spreadsheet in an older version of Excel, don’t use this function.

That being said, this function is pretty useful and greatly improves the old way of nesting an ISERROR function within an IF function.

Here are the required inputs for the IFERROR function:

It is pretty straightforward. The first input, value, is a formula that you would like calculated and then displayed. The second input, value_if_error, is what Excel will display in the event that the first input results in an error. As always, an example is the best way to learn it:

  1. Download the example spreadsheet here. Yes it is the same file from last week.
  2. In column F we’d like to calculate the revenue generated per call to the customer. Go to cell F3 and type in “=C3/D3” then copy the formula to the rest of the rows.
Read the full post (est. reading time 1:18 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | Leave a comment

Excel Tip of the Week: IF

Posted on March 2, 2012 by Doug Midkiff

Today’s topic is an introduction to the IF function.

The IF function is great, but it sort of has a bad reputation because it has been abused so much. The trick to effectively using the IF function is to keep it simple. Today I will show you a very basic example and hopefully make the function more accessible.  Here are the required inputs of the IF function:

Here it is in English: IF(Test this condition, if the condition is TRUE give me this answer, if the condition is FALSE give me this answer)

 I say it every time, but an example will be the best way to learn this. Here we go:

1)      Download the example spreadsheet here.
2)      The scenario: We have customers and the number of sales calls each customer received before they bought from us. We would like to label each customer as “High-touch” or “Low-touch” based on number of sales calls [more than 5 is “High-touch”]
3)      Go to cell E3 and type in this formula: =IF(D3>$I$7,”High”,”Low”)
4)      Copy the formula down
5)      Good job

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