If you're not presently using look-up formulas in your spreadsheets then you're likely spending far more time building spreadsheets than necessary. . In this live workshop work alongside Excel expert David Ringstrom, CPA, as he shows you the ins-and-outs of using look-up functions in Microsoft Excel. You'll start with the VLOOKUP function and master the arguments and situations where VLOOKUP can save you time and improve the integrity of your spreadsheets. You'll also work through troubleshooting techniques, and develop an eye for the nuances amidst your data that can cause look-up functions to return an error.
As the session progresses you'll learn alternatives to VLOOKUP, such as HLOOKUP for looking up data across rows, instead of down columns. After a discussion of ways to improve the integrity of VLOOKUP, you'll also master the INDEX and MATCH functions, along with SUMIF and SUMIFS. You'll also contrast using the ISERROR, IFERROR, and IFNA worksheet functions for displaying something other than an error like #N/A in your spreadsheet.
Pivot tables are one of Excel's most powerful features because they enable users to quickly and easily summarize data and create reports from complex data. In this live workshop work alongside Excel expert David Ringstrom, CPA, as he shows you the ins-and-outs of using pivot tables in Microsoft Excel. Bring your laptop so that you can instantly create reports from lists of data in Excel. You'll start by initiating a pivot table from a list of data, add fields, dig deep into the numbers, and much more. David will demonstrate a variety of techniques, including how to filter, format, and sort data. Like many Excel features, pivot tables have nuances that can be frustrating, so David points out traps and shares tricks to help ensure your reports are always accurate and self-maintaining.
As the session progresses you'll interact with your pivot tables by grouping and filtering data. David will show you hidden shortcuts, improving the integrity of pivot tables, and incorporating calculations within or alongside pivot tables. You'll also learn how to implement helpful pivot table features, including the Table feature, the Recommended Pivot Tables feature, the Slicer feature, the Power Pivot feature, and others.
Why should you attend this seminar?
Practitioners who can benefit by using lookup functions to improve the integrity of their worksheets and to work more efficiently in Excel.
Practitioners who wish to learn how to use Excel pivot tables to easily summarize data and create accurate reports.
Areas Covered in the Session:Morning:Firsthand Excel: Look-up Formulas
Afternoon: Firsthand Excel: Mastering Pivot Tables
- Improving the integrity of spreadsheets with Excel's VLOOKUP function.
- Exploring why VLOOKUP sometimes returns #N/A instead of a desired result.
- Future-proofing VLOOKUP by using Excel's Table feature versus referencing static ranges.
- Utilizing Excel's IFERROR function to display alternate values when VLOOKUP returns an error.
- Seeing what types of user actions can trigger #REF! errors.
- Implementing Data Validation to ensure that users make choices that VLOOKUP will recognize as valid.
- Using VLOOKUP to look up data from another workbook.
- Simplifying multiple-field look-ups with concatenation (combining fields together int a single cell).
- Troubleshooting other errors VLOOKUP can present such as #REF!, #NAME!, and #VALUE!.
- Seeing how the HLOOKUP function enables you to perform horizontal matches.
- Diagnose #N/A errors that arise when numbers are stored as text in Excel, or text contains extraneous spaces.
- Transform numbers stored as text into values by way of the Text to Columns wizard.
- Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
- Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
- Seeing why SUMIF is generally superior to VLOOKUP when looking up numbers in Excel.
- Using the SUMIFS function to sum values based on multiple criteria.
Who can Benefit:
- Identifying the requirements of ideal data sets to be analyzed within your pivot tables.
- Compiling unwieldy data into the format required for pivot table analysis quickly and easily.
- Verifying that a pivot table is referencing all the data you're expecting to be summarized.
- Using a simple keyboard shortcut to post the same formula to multiple cells at once.
- Learning how to control multiple pivot tables and charts instantly with the Slicer feature in Excel 2010 and later.
- Disabling the GETPIVOTDATA function if it's not needed for your analysis.
- Using the Report Filter command to create breakout tables and dig deeper into the numbers.
- Learning how to instantly determine the number of duplicates in a list with a pivot table.
- Drilling down into numbers with a double-click-or preventing other users from being able to do so.
- Learning how to expand and collapse pivot table elements, thereby avoiding information overload.
- Avoiding disabled features by converting Excel 97-2003 files to modern workbook formats with ease.
- Discovering the Recommended PivotTables feature in Excel 2013 and later.
- Understanding why numeric data may appear in a pivot table more than once and how to correct the problem.
- Understanding why pivot tables sometimes display amounts as text or count amounts instead of summing.
- Learning multiple ways to remove fields from a pivot table.
- Staving off frustration by filling blank cells within any columns that contain numbers with zeros before you create pivot tables.
- Learning the nuances associated with subtotaling data within a pivot table.
- Who Use lookup functions that allow them to work more efficiently.
- Create flexible data aggregations using pivot tables
- Represent data visually using pivot charts
- Calculate margins and other common ratios using calculation on pivot table
- Filter data using slicers in multiple pivot tables
- Create aggregate reports using formula based techniques