too Welcome to IJONA SKILLS

One Day Seminar on Excel: Pivot Tables and Look-Up Functions

This Webinar qualifies for a recertification credit hour that may be counted toward SHRM-CP and SHRM-SCP recertification from SHRM.
* This Lecture contains 8 credits.

Overview

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
  • 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.
Afternoon: Firsthand Excel: Mastering Pivot Tables
  • 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 can Benefit:
  • 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 

Morning:Firsthand Excel: Look-up Formulas
  • 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.
Afternoon: Firsthand Excel: Mastering Pivot Tables
  • 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.
Generic placeholder image

David H. Ringstrom

David H. Ringstrom, CPA Keynote Speaker at Ijona Skills. He is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David's mantra is "Either you work Excel, or it works you," so he focuses on what he sees users don't, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively.

Address:
Four Points by Sheraton Miami Airport
3570 Nw 74th Ave
Miami FL 33122 United States Status:
Phone:(305) 351-5730



If you are in a group of 2-10. Select "Multiple Attendee" and get upto 30% discount.

Single Attendee [Live]

$850.0

Single Attendee [Live+Recorded]

$1699.0

Multiple Attendee [Live]

$1700.0 
save $170.0






No. of Attendees: 
 10% off

Multiple Attendee [Live+Recorded]

$3398.0 
save $339.80






No. of Attendees: 
 10% off

Single Attendee [Recorded]

$1299.0

Unlimited Attendee [Recorded]

$2999.0

Select Meal: 

    Veg       Non Veg







For group of 10+ attendee registrations,
contact customer care at: +1-717-208-866, +1-302-830-3132

The registration fee includes: the workshop; all course related materials; tea/coffee and lunch.


South Beach

No visit to Miami can be considered complete without a stop at South Beach -- the quintessential Miami hot spot. From shopping to partying, this area of Miami Beach is well-known for being a trendy locale. Depending on your personal tastes, you might enjoy spending a full weekend touring South Beach.

Seaquarium

The Miami Seaquarium is located right in the middle of the tourist area, on the causeway between downtown Miami and Key Biscayne. It's a fabulous stop where you can witness an outdoor aquarium experience that's only possible in our tropical climate. Be sure to budget enough time to spend at least half a day there!

Miami Museum of Science

Check out the latest kid-friendly exhibits at the Miami Museum of Science. You're bound to find a learning adventure for the whole family. The museum is home to the Bird of Prey Research Center and the Weintraub Observatory.If you have kids (or just like to act like them!), the Miami Children's Museumis a must-see destination. 

Everglades

With 1.5 million acres of swamps, saw-grass prairies, and sub-tropical jungles, Everglades National Park is one of the most unusual public parks in the United States. Located on the southern tip of Florida, the park is home to 14 rare and endangered species, including the American Crocodile, the Florida Panther, and the West Indian Manatee.

Seminar Id:ISDR0203
image  Speaker: David H. Ringstrom
calender   Date: 03/02/2018
clock  Start Date: 06:00 AM PT | 09:00 AM ET
map  Place: Miami, Florida USA
Seminar Fee Includes:
lunch Lunch
tea AM-PM Tea/Coffee
material Seminar Material
hard_copy Hard copy of presentation
certificate Attendance Certificate
Upcoming Seminar of David H. Ringstrom
One Day Seminar on Excel: Pivot Tables and Look-Up Functions
By: David H. Ringstrom

Date: 03/02/2018 | Time: 06:00 AM PT | 09:00 AM ET
Price:$ 850.0
Status: Upcoming

More Seminar

Upcoming Seminar :Soft Skills
"Virtual Team" - Managing People Effectively in Multiple Locations
By: Chris DeVany

Date: 02/23/2018 | Time: 6:00 AM PT | 9:00 AM ET
Price:$ 850.0
Status: Upcoming

One Day Seminar on Excel: Pivot Tables and Look-Up Functions
By: David H. Ringstrom

Date: 03/02/2018 | Time: 06:00 AM PT | 09:00 AM ET
Price:$ 850.0
Status: Upcoming

More Seminar


top