MS EXCEL 2003: FILTER A SINGLE COLUMN BASED ON 3 OR MORE CRITERIA

This Excel tutorial explains how to filter a single column based on three or more criteria in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Excel 2003/XP/2000/97, I have an Excel spreadsheet and I would like to filter more than 2 types of criteria from a single column. The custom AutoFilter only allows for up to 2 at a time. How can filter a single column based on 3 or more criteria?

Answer: You can filter a single column based on 3 or more criteria by applying an advanced filter. To do this, open your Excel spreadsheet so that the data you wish to filter is visible.

In a blank column, add the column heading and the values that you'd like to filter on. In this example, we want to filter the Order ID column to display the orders 10248, 10251, and 10253. We've typed these values into column F.

Highlight the data that you wish to filter. We've highlighted columns A to D.

Under the Data menu, select Filter > Advanced Filter.

When the Advanced Filter window appears, the List range field should display the data that you highlighted in the previous step.

Next, select the Criteria range. These are the filter values. In our example, we've entered the filter values into cells F1 to F4. (ie: Order IDs 10248, 10251, and 10253). Then click on the OK button.

Now when you return to your spreadsheet, your data should be filtered. (Note: The row numbers on the left will appear in blue when your data has been filtered.)

MS EXCEL 2003: USE AN ARRAY FORMULA TO SUM VALUES IN COLUMN A WHEN VALUE IN COLUMN B AND COLUMN C MATCH CRITERIA

This Excel tutorial explains how to use an array formula to sum the values in one column when a value in two other columns match a criteria in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, I need to create a formula that will sum all the values in Column A when the value on the same row in Column B is 150 and the value in Column C is the letter U.

Answer: This can be done in Excel with an array formula.

Let's look at an example.

In cell A7, we've created the following array formula:

=SUM((A1:A5)*(B1:B5=150)*(C1:C5="U"))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM((A1:A5)*(B1:B5=150)*(C1:C5="U"))}

MS EXCEL: TWO-DIMENSIONAL LOOKUP 

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #1.

Question: I'm trying to reference a particular cell within an xy axis chart and can't find the formula or function that allows me to do so.

For example A1 needs to equal where row 12 intersects column F on a chart.

I know the lookup function can get me a value from a known array of values located in the corresponding column, but I can't get it to figure from an array of columns. Can you help?

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a vlookup function and a match function.

Let's look at an example to see how you would use this function in a worksheet:

In the spreadsheet above, we have a listing of products (Oranges, Apples, Bananas, Pineapples, Watermelons) and a listing of quantity columns (5 lbs, 10 lbs, 15 lbs, and 20 lbs). What we want to do is find the correct value based on a product and quantity combination.

In the first case, we want to find the price/lb for 10 lbs of oranges. To find the price/lb, we've entered the following formula into cell D17:

=VLOOKUP(B17, $B$8:$F$13, MATCH(C17, $B$8:$F$8, 0), FALSE)

This formula returns the value of $4.80.

In the second example, we are looking for the price/lb for 5 lbs of bananas. We've entered the following formula into cell D18:

=VLOOKUP(B18, $B$8:$F$13, MATCH(C18, $B$8:$F$8, 0), FALSE)

This formula returns the value of $1.50

5 Keyboard Shortcuts for Rows and Columns in Excel

Bottom line: Learn some of my favorite keyboard shortcuts when working with rows and columns in Excel.

Skill level: Easy

Whether you are creating a simple list of names or building a complex financial model, you probably make a lot of changes to the rows and columns in the spreadsheet.  Tasks like adding/deleting rows, adjusting column widths, and creating outline groups are very common when working with the grid.

This post contains some of my favorite shortcuts that will save you time every day.

#1 –  Select Entire Row or Column

Shift+Space is the keyboard shortcut to select an entire row.

Ctrl+Space is the keyboard shortcut to select an entire column.

The keyboard shortcuts by themselves don’t do much.  However, they are the starting point for performing a lot of other actions where you first need to select the entire row or column.  This includes tasks like deleting rows, grouping columns, etc.

When you press the Shift+Space shortcut the first time it will select the entire row within the Table.  Press Shift+Space a second time and it will select the entire row in the worksheet.

The same works for columns.  Ctrl+Space will select the column of data in the Table.  Pressing the keyboard shortcut a second time will include the column header of the Table in the selection.  Pressing Ctrl+Space a third time will select the entire column in the worksheet.

You can select multiple rows or columns by holding Shift and pressing the Arrow Keys multiple times.

#2 – Insert or Delete Rows or Columns

There are a few ways to quickly delete rows and columns in Excel.

If you have the rows or columns selected, then the following keyboard shortcuts will quickly add or delete all selected rows or columns.

Ctrl++ (plus character) is the keyboard shortcut to insert rows or columns.  If you are using a laptop keyboard you can press Ctrl+Shift+= (equal sign).

Ctrl+- (minus character) is the keyboard shortcut to delete rows or columns.

So for the above shortcuts to work you will first need to select the entire row or column, which can be done with the Shift+Space or Ctrl+Space shortcuts explained in #1.

If you do not have the entire row or column selected then you will be presented with the Insert or Delete Menus after pressing Ctrl++ or Ctrl+-.

You can then press the up or down arrow keys to make your selection from the menu and hit Enter.  For me it is easier to first select the entire row or column, then press Ctrl++ or Ctrl+-.

So, the entire keyboard shortcut to delete a column would be Ctrl+Space, Ctrl+-.  You could also use the keyboard shortcut Alt+H+D+C to delete columns and Alt+H+D+R to delete rows.  There are lots of ways to do a simple task… 

#3 – AutoFit Column Width

There are also a lot of different ways to AutoFit column widths.  AutoFit means that the width of the column will be adjusted to fit the contents of the cell.

You can use the mouse and double-click when you hover the cursor between columns when you see the resize column cursor.

The AutoFit Column Width button bases the width of the column on the cells you have selected.  In the image above I have cell A4 selected.  So the column width will be adjusted to fit the contents of A4, as shown in the results below.

Alt,H,O,I is the keyboard shortcut for the AutoFit Column Width button.  This is one I use a lot to get my reports looking shiny. 

Alt,H,O,A is the keyboard shortcut to AutoFit Row Height.  It doesn’t work exactly the same as column width, and will only adjust the row height to the tallest cell in the entire row.

#4 – Hide or Unhide Rows or Columns

There are several dedicated keyboard shortcuts to hide and unhide rows and columns.

• Ctrl+9 to Hide Rows
• Ctrl+0 (zero) to Hide Columns
• Ctrl+Shift+( to Unhide Rows
• Ctrl+Shift+) to Unhide Columns – If this doesn’t work for you try Alt,O,C,U (old Excel 2003 shortcut that still works).

The buttons are also located on the Format menu on the Home tab of the Ribbon.  You can hover over any of the items in the menu and the keyboard shortcut will display in the screentip. The trick with getting these shortcuts to work is to have the proper cells selected first.

To hide rows or columns you just need to select cells in the rows or columns you want to hide, then press the Ctrl+9 or Ctrl+Shift+( shortcut.

To unhide rows or columns you first need to select the cells that surround the rows or columns you want to unhide.  In the screenshot below I want to unhide rows 3 & 4.  I first select cell B2:B5, cells that surround or cover the hidden rows, then press Ctrl+Shift+( to unhide the rows.

The same technique works to unhide columns.

#5 – Group or Ungroup Rows or Columns

Row and Column groupings are a great way to quickly hide and unhide columns and rows.

Shift+Alt+Right Arrow is the shortcut to group rows or columns.

Shift+Alt+Left Arrow is the shortcut to ungroup.

Again, the trick here is to select the entire rows or columns you want to group/ungroup first.  Otherwise you will be presented with the Group or Ungroup menu.

Alt,A,U,C is the keyboard shortcut to remove all the row and columns groups on the sheet.  This is the same as pressing the Clear Outline button on the Ungroup menu of the Data tab on the Ribbon.

*Bonus funny: At some point when using the group/ungroup shortcuts, you will accidentally press Ctrl+Alt+Right Arrow.  This is a Windows shortcut that orientates the entire screen to the right.  I call it “neck ache view”.  To get it back to normal press Ctrl+Alt+Up Arrow.

Variance on Clustered Column or Bar Chart

his post will explain how to create a clustered column or bar chart that displays thevariance between two series.

Clustered Column Chart with Variance

Clustered Bar Chart with Variance

Overview

The clustered bar or column chart is a great choice when comparing two series across multiple categories.  In the example above, we are looking at the Budget versus Acutal (series) across multiple Regions (categories).  The basic clustered chart displays the totals for each series by category, but it does NOT display the variance.  This requires the reader to calculate the variance manually for each category.

However, the variance can be added to the chart with some advanced charting techniques.  A sample workbook is available for download below so you can follow along.

Data Requirements

With any chart, it is critical that the data is in the right structure before the chart can be created.  The following image shows an example of how the data should be organized on your sheet.  It is a simple report style with a column for the category names (regions) and two columns for the series data (budget & actual data).

This technique only works when comparing two different series of data.  This can include a comparison of any data type: budget vs. actual, last year vs. this year, sale price vs. full price, women vs. men, etc.  The number of categories is only limited to the size of the chart, but typically you want to have five or less for simplicity.

Chart Requirements

The chart utilizes two different chart types: clustered column/bar chart and stacked column/bar chart.  The two data series we are comparing (budget & actual) are plotted on the clustered chart, and the variance is plotted on the stacked chart.

The chart also utilizes two different axes: the comparison series is plotted on the secondary axis, and the variance is plotted on the primary axis.  This puts the stacked chart (variance) behind the clustered chart (budget & actual).

How-to Guide

Data Calculations

The first step is to add three calculation columns next to your data table.

• Variance Base – The base variance is calculated as the minimum of the two series in each row.  This gives you the value for plotting the base column/bar of the stacked chart.  The bar in the chart is actually hidden behind the clustered chart.
  _
• Positive Variance – The variance is calculated as the variance between series 1 and series 2 (actual and budget).  This is displayed as a positive result.  An IF statement is used to return a blank value if the variance is negative.  The blank value will not be plotted on the chart, and no data label will be created for it.
  _
• Negative Variance – This is the same basic calculation as the positive variance, but we use the absolute function (ABS) to return a positive value for the negative variance.  The negative variance needs to be plotted as a positive value to bridge the gap between the two series.  Calculating this in a separate column allows us to assign the negative series a different color, so the reader can easily differentiate it from the positive variance.
  

How to Create the Chart

The example file (free download below) contains step-by-step instructions on how to create the column version of this chart.  Creating the bar chart is the exact same process with stacked and clustered bars instead of columns.

The chart is not too difficult to create, and provides an opportunity to learn some advanced techniques.

1. The first step is to create a Stacked Column Chart and add the five series to it.
   _
   _
2. Series 1 (Actual) and Series 2 (Budget) need to be plotted on the secondary axis.  Right-click on the Actual series column in the chart, and click “Format Data Series…”
   
   _
   Select the “Secondary Axis” radio button from the Series Options tab.
   
   _
   Repeat this for the Budget Series (series 2)._
   
3. Change the chart type for series 1 & 2 to a Clustered Column Chart.  Select the Actual series in the chart, or in the Chart Elements drop-down on the Layout tab of the Ribbon (chart must be selected to see the Chart Tools contextual tab).
   
   _
   Click the Change Chart Type button on the design tab and change the chart type to a Clustered Column chart.
   .
   _
   We can now start to see the chart take shape.  The Acutal and Budget data are displayed in side-by-side columns for comparison.  The Variance series are displayed in the background as a stacked column.
   
   _
4. Adjust the Gap Width property for both charts.  The gap width can be changed in the Series Options tab of the Format Data Series window.  This controls the width of the columns.  A smaller number will create a larger column, or smaller gap between categories.
   
   _
5. Format the chart.  The chart is just plain ugly with its default formatting options.  We can make a few adjustments to make it more presentable.
   – Move the legend to the top and delete the 3 variance series.
   – Add a Chart Title.
   – Delete the Axis Labels.
   – Change the border and fill colors for the columns.
   – Delete the horizontal guidelines.
   _
   _
6. Add the data labels.  The variance columns in the data table contain a custom formatting type to display a blank for any zeros:
   _(* #,##0_);_(* (#,##0);_(* “”_);_(@_)
   These blanks also display as blanks in the data labels to give the chart a clean look.  Otherwise, the variance columns that are not displayed in the chart would still have data labels that display zeros.
   
   _
   The data labels for a stacked column chart do not have an option to display the label above the chart.  So you will have to manually move the variance label above, and to the left or right of the column.

Summary

This chart is a great way to display the series data and the variance amount in one chart.  The guide is meant to help you understand how to create and edit these charts to tell your story.  The source data table is simple in structure, and the chart can be re-used with different data so you do not have to go through this process every time.

Please click here to subscribe to my free email newsletter to receive more great tips like this.  You will also receive a free gift.  It’s a win-win! 

Download

 Variance on Column or Bar Chart Guide.xlsx (185.7 KB)

The file below uses a slightly different technique by using a clustered column chart to display the variance, and then uses the Value from Cells option to display the data labels.  This only works in Excel 2013.  The advantage is that you can automatically display the variance label above the bar, and you don’t have to move it manually as the numbers change.