Nonprofit Accounting Basics

Everyday Excel Data Analysis Tips and Techniques

Topics: 

Excel data analysis features are widely used in the nonprofit and for-profit business communities. Once you become proficient with these and other features, you will be able to use Excel for managing lists of data and analyzing the data to identify important trends and subtotals. This article will help you master “must know” features in Excel to help you save time each day and increase your productivity. The four tips presented below include screen shots and steps to follow. You can create similar examples on your computer and try the steps as presented. The four tips covered are how to:

  1. Find duplicates within lists (Excel 2007 or 2010)
  2. Use AutoFilter to filter by color (Excel 2007 or 2010)
  3. Use the enhanced Data Sort dialog box (Excel 2007 or 2010)
  4. Calculate automatic subtotals within a list (Excel 2003, 2007 or 2010)

Before beginning work on a list in Excel, it is recommended that you check that your list is designed following specific rules. These rules are:

  1. the list must be a continuous angle with no completely empty columns or completely empty rows
  2. the column headings must be on the top row (one row only) with one formatting feature across the column headings
  3. the data type within each column must be all text or all values
  4. if you place any of your own formulas below the list you must skip at least one row

When you follow these rules, you will never have to manually select the whole list to perform data analysis in Excel. You only need to select a single cell within the list and Excel will select the entire list for you.

1. Find duplicates within lists

Excel De-Dupe

The enhanced Conditional Formatting feature in Excel 2007 and 2010 allows you to find duplicates within a list in just five clicks of the mouse. Suppose you need to know if there are duplicate check numbers or duplicate member numbers within a list. Follow the list design rules just mentioned and select the entire column you need to check for duplicates.

Click the Home tab on the Ribbon, click the Conditional Formatting tool, click Highlight Cell Rules, click Duplicate Values and click OK. Excel applies the color red to the cells to identify the duplicates. You can scroll to see where the color is applied, or if the list is large, you can activate the AutoFilter feature and filter or sort by the color red.

To remove the Conditional Formatting rule from the cells, click the Home tab on the Ribbon, click the Conditional Formatting tool, click Clear Rules and click Clear Rules from Entire Sheet.

2. Use AutoFilter to filter by color

Color

When you apply color to cells in a list either manually with the fill color tool or as a result of conditional formatting rules as in tip #1, the AutoFilter feature is now able to filter and sort by color. This can save time when working with very long lists. You can avoid scrolling up and down to see the cells with color by turning on AutoFilter and selecting filter by color.

Place the cell pointer in the list and click the Filter tool on the Data tab on the Ribbon. From the drop-down list triangle located at the top of the column with color applied, select Filter by Color. The rows without color are temporarily hidden. To turn off the filter, click the same triangle and select “Clear filter” from that column.

3. Use the enhanced Data Sort dialog box

Enhanced Sort

In Excel 2007 and 2010, the Data sort dialog box allows you to sort by up to 64 columns at one time. This may sound excessive, but it allows more flexibility compared to Excel 2003 where the Data sort dialog box only allowed three columns to be sorted at a time.

Let’s use an example where we have a list of divisions and departments and employees allocating time worked. We need to show the hours worked and cost of the time worked subtotaled by department and division. This requires the list to be sorted. The order of this sort will be by division, then department, then last name, and then first name. The column headings of the list are shown here.

Click on a single cell within the list. Click the Data tab on the ribbon, click the Sort tool, and click the “Add Level” button three times to have a total of four boxes to use for sorting. In the lowest box, click the triangle and select the FirstName field. In the 3rd box down, click the triangle and select the LastName field. In the 2nd box down, click the triangle and select the Department field. In the top box, click the triangle and select the Division field. Click OK and notice that the list is now sorted by division, then by department, then by last name, then by first name. Another way to describe the result of the sort is that the list has the division rows grouped together and within the divisions the department rows are grouped together. This grouping allows for the automatic subtotals feature to work.

Column Headings

 

 

4. Calculate automatic subtotals within a list

Subtotals

In the next steps, the list is already sorted and we can have Excel calculate the subtotals of costs by division and by department in just a few clicks of the mouse. The sorting was accomplished in Tip #3.

Now, with a single cell selected in the list, click the Data tab on the Ribbon, click the Subtotal tool, click the triangle below “At each change in” and select Division. Leave Sum as the choice under “Use function:” and place check marks in the boxes to the left of Hours and Pay in the “Add subtotals to:” section of this dialog box. Click OK.

The result is that Excel inserted rows between the grouped division rows and added the subtotals on those rows for the hours worked and pay columns. Also, a grand total was added at the bottom of the list.

In the upper left corner of the worksheet, you will notice grouping and outlining tools. Click the tools with the numbers 1, 2, and 3 and you will see Excel hide the detail rows and show only the subtotals. The + symbols are show/hide tools which can be used to show detail at individual levels of the subtotals.

Subtotals

Since we had also previously sorted the list by departments within the divisions, we can add more subtotals at each change in department. Click the Data tab on the Ribbon, click the Subtotal tool, click the triangle below “At each change in” and select Department. Leave Sum as the choice under “Use function:” and leave the check marks in the boxes to the left of Hours and Pay in the “Add subtotals to:” section of this dialog box. To add these subtotals in addition to the other subtotals already in the list, you must uncheck “Replace current subtotals.” Click OK. The result is that Excel inserted rows between the departments and placed subtotals on those rows.

To remove all of the subtotals, click the Data tab on the Ribbon, click the Subtotal tool, and click the “Remove All” button in the lower left corner of the dialog box. Excel removes all of the rows that were added for the automatic subtotals.

Automatic Subtotals is a very popular feature because Excel is doing the majority of the work for you. Many Excel users manually insert rows and build the subtotals manually because they are unaware of this time-saving feature.

If you enjoyed these tips, watch for an upcoming article that will discuss the incredible Excel Pivot Table feature for data analysis.