MACPA Technology Conference 10/4/2012
Microsoft Excel 2010 Formulas and Functions Presenter: Judy Borsher, CPA SCG Training + Consulting Corporation
Microsoft Excel 2010 Formulas and Functions Explore New Functions added in Excel 2007/2010: SUMIFS, COUNTIFS, IFERROR, AGGREGATE
Use Data Cleanup Functions: DATEVALUE, LEN, LEFT, RIGHT, SEARCH, MID, TRIM
Use Logical functions: IF, OR, AND, nested functions Use functions and features to compare two lists of data and remove duplicates Consolidate across sheets with formulas vs. Multiple Consolidated Ranges Use features that automatically build formulas: Subtotals, Pivot Tables, Table
SCG‐training.com
1
MACPA Technology Conference 10/4/2012
Judy Borsher CPA, CGMA, MBA, CITP, MCT • CPA, CITP with 30 years of accounting and technology business experience, including public accounting with KPMG, finance positions, and a technology consulting practice • SCG Training & Consulting Corporation • Creator and presenter of technology CPE programs in the Washington DC / Maryland / VA area for over 20 years • Partner with the Business Learning Institute of the MACPA • National speaker including technology conferences of the MACPA, VSCPA, GWSCPA and AICPA • Microsoft Certified Trainer • BA and MBA Cornell University
New Functions in Excel 2007/2010 SUMIFS COUNTIFS IFERROR AGGREGATE
SCG‐training.com
2
MACPA Technology Conference 10/4/2012
Functions for Data Cleanup in Lists DATEVALUE LEN LEFT RIGHT SEARCH MID TRIM
IF, OR, AND, nested functions
SCG‐training.com
3
MACPA Technology Conference 10/4/2012
Compare two lists of data Vlookup and Concatenate
Conditional Formatting – Find Duplicates
Turn on Conditional Formatting to find duplicates after selecting cells. Cells with duplicates will appear red. Turn off Conditional Formatting by selecting Clear Rules. Use AutoFilter to “filter by color” to show only the rows with duplicates.
SCG‐training.com
4
MACPA Technology Conference 10/4/2012
Filter based on color
With the cell pointer in the list, click the Filter tool on the Data tab on the Ribbon. From the drop-down list on the column with duplicates, select Filter by Color.
Consolidate across sheets 3 D Sum vs. Multiple Consolidated Ranges
SCG‐training.com
5
MACPA Technology Conference 10/4/2012
Subtotals, Table, Pivot Tables
Rules for Working with Lists
The list must be a continuous rectangle with no empty rows or columns. The column headings must be on only one row at the top. The column headings must be unique. The data in each column should be all values or all text.
SCG‐training.com
6
MACPA Technology Conference 10/4/2012
The New Data Sort Dialog Box
The Excel 2010 sort dialog box allows for up to 64 column choices whereas Excel 2003 allowed for 3 column choices.
Add Automatic Subtotals Sort the list first so that the a column of information is grouped together. Excel follows the pattern of the groups for the subtotals. Select Data | Outline | Subtotal from the Ribbon. In the dialog box, choose the column to watch for grouping “at each change in” and check off the columns to be calculated. The result is outlining in the upper left and subtotals using the subtotal function between the groups.
SCG‐training.com
7
MACPA Technology Conference 10/4/2012
Remove Automatic Subtotals
Place the cell pointer in the list. Select Data | Outline | Subtotal from the Ribbon. In the dialog box, click the Remove All button in the lower left corner. Excel removes all the rows, subtotals, and outlining features that were added.
The New Table Feature Create a Pivot Table based on a TABLE Create a Table based on a list Apply Table Styles Use the interactive Table Range Name Build a Pivot Table based on a List Build a Pivot Table based on a Table Update the List and Pivot Table
SCG‐training.com
8
MACPA Technology Conference 10/4/2012
The New Excel Table
1.
2.
3.
4.
This exciting new feature for manipulating lists can be activated with the Table tool on the Insert Ribbon tab. Place the cell pointer within the range of a list and select the Table tool. Automatic color banding, subtotals and filters are active. Activate the Total Row as shown to add subtotals.
Advantages of a Table Eight different features activate at once. 1. 2. 3. 4. 5. 6.
7. 8.
SCG‐training.com
Automatic color banding is applied to the list and adjusts to inserted rows. Inserted rows automatically apply formatting to the new row and copy formulas down. Live preview is active when selecting a table style from the Design tab on the Ribbon. AutoFilter is active and the Total Row calculates to the visible filtered cells. The column headings are displayed in place of the column letters when scrolling down. The Total Row choice on the Design tab on the Ribbon activates the subtotal function on the bottom row. Select the calculations from the drop-down lists on the Total Row cells. The Total Row can be turned off and on at anytime. Turn off the Total Row and type at the bottom of the list. The marker in the lower right corner cell tracks the list size as it grows or shrinks. On the left end of the Design tab you will see the Table name, which is an interactive range name, and it can be referenced in Pivot Tables.
9
MACPA Technology Conference 10/4/2012
Using a Table Name for a Pivot Table Range One of the main advantages of the Table name is to track the size of the list. If the list is updated regularly and Pivot Tables are created from the list, it makes sense to make the list a Table “before” creating the Pivot Tables. Use the Table name as the Pivot Table range. When the Pivot Table needs to be recalculated, the Pivot Table will recalculate to the entire Table, even if someone typed new rows at the bottom of the table.
Build a Pivot Table Place the cell pointer in a list. From the Insert tab on the Ribbon, select PivotTable. Notice that Excel has selected the coordinate range. Click OK. Drag the fields (column headings) on the right into the appropriate boxes in the lower right. Excel uses the Row Labels to show detail in column A. Column Labels show detail along Row 3. The Values box generates calculations by category. You can also check off the fields and allow Excel to place the field objects into the boxes.
SCG‐training.com
10
MACPA Technology Conference 10/4/2012
Compare Revenue Results over time by category
Generate separate reports by category based on the Report Filter First drag a descriptive field into the Report Filter such as location, account, etc. Select the Options tab, click the drop-down on the Options tool, click Show Report Filter Pages, and click OK. The result is separate Pivot Tables for each unique item in the Report Filter, each on separate sheets.
SCG‐training.com
11
MACPA Technology Conference 10/4/2012
Automatically group transaction dates into date ranges of months, quarters or years In the Pivot Table, right-click on a date and select Group. In the Group dialog box, select Years and Months or Years and Quarters. You can also select a start and end date to set restrictions on the data set.
Pivot Table Tips You can type over the labels in the Pivot Table to adjust wording for clarity On the Design contextual tab on the Ribbon, you can apply Pivot Table styles for automatic color banding Recalculate a single Pivot Table if underlying data changes: right-click on the Pivot Table and update Recalculate all Pivot Tables if underlying data changes: right-click on the Pivot Table and Refresh All Format numbers in the Pivot Table: right-click on a number; select Number Format; choose Number, set the decimal places; click “,” and choose format.
SCG‐training.com
12
MACPA Technology Conference 10/4/2012
Office 2010 Learning Links at the Microsoft Website
Training modules > http://office.microsoft.com/en-us/support/trainingFX101782702.aspx
Interactive Command Guides and Command Lists > http://office.microsoft.com/en-us/outlook- help/learnwhere-menu-and-toolbar-commands-are-in-office2010-HA101794130.aspx
Receive Office 2010 Templates
If you would like to receive template files including files used in this presentation, please send your request to:
[email protected] For more information, contact: Judy Borsher 301-585-1174
Thank you for your participation!
SCG‐training.com
13
