Data Forms and Subtotal Tools
Working with Data Forms in Excel
Many of us track and analyze data in Microsoft Excel. Eventually, most of us find ourselves working with large spreadsheets to key enormous amounts of data. And that introduces a limitation of Excel: When data spreads along a worksheet horizontally, the task of constantly scrolling left and right to continue to key data can be tedious. Fortunately, Excel offers several types of data forms to make spreadsheets more efficient. One of the most useful forms to tame huge data files is Data Forms.
In the image below, I am unable to view all my columns in one window. (Note: Click on any image to see a larger version.) For data entry purposes, I would have to zoom out to determine when I come to the end of a row of data or use the horizontal scroll bar to move it to the right and view the remaining columns.
In this article we’ll find that, with a data form, data entry becomes a breeze to find, enter, and delete records in large data sets.
To Enable Data Forms
- Click on the Quick Access Tool Bar at the top left hand column
Click More Commands - Choose All Commands, under Choose commands from section
Options are organized alphabetically; just scroll to Forms and click the Add button - The Form icon will now appear on the Quick Access Tool bar
To Set Up the Data in Data Forms
- Column labels are required for this feature to work
Once column labels are in place, select the appropriate column labels in Row 1 (be careful not to select the entire row or the entire columns; an error will occur if the whole row or columns are selected). Select the new Forms button (I’ve labeled it “2” on the Quick Access Tool bar image above) - Press OK
- Begin to create new records, find records, or delete records
Tab still works to move from field to field. No more messing with the scrollbar to be able to see the data
Working with Records
When creating a new spreadsheet or adding records to an existing one, data forms can be a tremendous time saver. Start by using the same steps above to enable data forms and begin utilizing this efficient feature.
- To create a new record, simply key data values into each field and press New
- This feature can be used to find records as well (with the commands Find Next and Find Prev); it is then possible to make edits to the found field(s)
One of the greatest benefits of this tool is a Criteria feature. Looking at the dataset in our example, I needed to be able to find all clients in my list from a particular state. For example, if I type “TX” in the State field and select Criteria, I’m able to find all clients from Texas.
Calculate Against Filtered Data
When working in Excel, many of us have used a filter on a long data list to see relevant information. While filtering is a great feature, it can make it tricky to calculate against a list when either filters are applied or hidden rows exist.
In the example below, we currently have row totals (or a sum formula) on our data (see row 15). Most calculations will work great until the data are filtered or hidden. Normal Excel functions will not calculate against filtered or hidden data.
Let’s take a look below at this example: What happens to the calculation if I were to filter information in this list? Would the calculations be correct?
I applied a filter on the Expense 5 column to see only the data with a $200 value
- Notice that the totals row no longer appears
- To recreate a total against the filtered data to add the filtered values together as =sum(F5:F10)
- We know that 200+200+200 does not equal 1715
We’ll discuss three ways to use the Subtotal:
- A Subtotal function
- Subtotal tables
- Subtotal feature
The Subtotal Function
The Subtotal function gives users a way to calculate and still have the options to filter or hide rows/columns. When creating spreadsheets for users to analyze, filtering becomes an essential part of data analysis.
- As with all formulas, begin the formula with the equal sign, then type SUBTOTAL
- Continue the formula by pressing the Tab key (or manually entering the open parenthesis symbol)
- The following list of options will display
These numbers represent a calculation for the function to run: Average, Count, Max, Min, and so forth - Options 1 through 9 will assume there is a filter applied (does not apply to hidden rows/columns)
- Options 101 through 109 will assume there is either a filter or hidden rows/columns
- The following list of options will display
- Double click on the number that represents the calculation to perform
- Type a comma, then identify the cells to calculate (either click and drag the cells or manually enter a cell range)
- Press Enter
Keep in mind that option 109 would be used for spreadsheets with hidden rows instead of option 9; otherwise, the calculation will be incorrect.
Not Comfortable with Writing Formulas?
Many Excel users struggle with the multitude of rules needed to write formulas. For those users, converting data to a table allows use of the subtotal feature without the need to write the formula.
Convert to Table
Converting data to a Table has many benefits. In addition to formatting and filters is the most amazing tool: Total Row.
- Click anywhere within the data, under the column labels
- From the Insert tab’s Table group, choose Table
- Press OK
- From the Design tab of the Table Tools menu, check the box next to Total Row
- Only in Excel, this will add a row below the final row of data
- Now, any cell selected in the Total row will show a drop down box allowing the user to select several different calculations for Excel to perform
The intent of converting data to a table is to perform quick and easy calculations… without the need to write any formulas.
When using tables for subtotals, the formula automatically uses the 101 to 109 values for all calculations (see formula bar above). This default value ensures that the Total row will calculate data correctly whether it is filtered or hidden.
Subtotal Feature
In this example, I will discuss the benefit of the Subtotal feature, which is found on the Data tab of the Outline group.
The Subtotal feature provides a quick and painless way to group related data and write a formula.
In the example above, I have a list of employees, with fields for their first name, last name, division, department, date of hire, hours worked, and hourly rate. I need to establish the average hours that employees work for any given department.
Excel will calculate the average hours for me, with only minor setup required. I start by sorting the data by department. Many Excel users would then create four separate formulas to get the average hours worked for each department. But we can have the Subtotal feature do all the backend work for us.
Subtotal Feature
- Once the list has been sorted, make sure a data cell is selected, then click Subtotal (in the Outline group within the Data tab)
- A new menu will pop up. The first editable field is At each change in:. This option identifies the column to group together or organize. (I want to group by department, so Excel looks at each cell in this column and will move vertically from cell to cell, looking for a change a change in text)
- Still in the pop up menu, select the correct option in the Use function: This option identifies the type of calculation desired
- In Add Subtotal to:, check the column labels Excel will use in the calculation
- Press OK
In the spreadsheet’s top left corner (see image above), a new Subtotal will display three different views:
- 3=A fully expanded group
- 2= The numerous groups created, along with the subtotals
- 1 = Grand total of all departments and total average hours worked
To remove Subtotals at any time, select some of the data from either of the three views, then click Subtotal again, then Remove All.
Excel’s Subtotal is an amazing tool. Some practice will help users to distinguish the full benefits behind each of the ways to use the tool. Be on the lookout for more tips to come from the NetGain Mnemonic. Please post to the Comments section if any of the instructions are unclear or if you have related questions.
