3 Hour Excel Automation Boot Camp
Cathy Horwitz believes that when your employees know the capabilities of the software they use, they will demonstrate improved productivity, will be more efficient, and will be able to problem-solve more easily.
Cathy teaches classes on the Microsoft suite of application software including Excel, PowerPoint, Word, Access, and Outlook. Cathy has over 30 years of experience in the classroom and virtual training and has been an instructor of Microsoft Office since 1989. Her strengths include customizing classes based on the needs of individual students and providing realistic business examples to complement the training. She is a high energy trainer with a flair for training the adult student.
She holds the Modern Classroom Certified Trainer certification from Logical Operations which prepares instructors for all aspects of delivering a course using the latest training technologies and approaches. When not teaching, Cathy enjoys shopping estate sales and refinishing mid-century furniture.
- Conditional Formatting and Data Validation
- Introduction to Excel PivotTables
- Excel Lookup Functions; VLOOKUP, MATCH, INDEX
With Conditional Formatting, you will learn how massive amounts of data can be analyzed to visually emphasize relationships, find exceptions, and spot trends. Conditional formatting can change the format of cells based on their values in multiple ways including font style, underline style, font color, fill color, and more. Learn how to display Data bars, Color scales, and use Icon sets.
To be sure that the data is entered accurately, use Data Validation to restrict the input type of a value based on a specific set of criteria. Trying to enter data that does not meet the criteria will generate an error message. Create a drop-down list of items in a cell with data validation. This will make data entry easier and reduce input errors and typing mistakes.
PivotTables can help you sort and sift through large data sets to focus quickly on just the data elements that matter most to your specific needs. In addition, you’ll get tips on the easiest way to group your data. Most importantly, when you use the power of PivotTables, you will be saving time and making your job easier.
Learning about the lookup functions will allow you to match data between databases. VLOOKUP enables you to quickly and easily look for value down a column of data and return a value from the same row in a different column. Perfect for when working with large tabular data. INDEX and MATCH work in a similar way but provide more flexibility.
Areas Covered
- Using Conditional Formatting
• Types of conditional formats
• Highlighting cells rules
• Applying data bars, color scales, and icon sets
• Removing conditional formatting
• Modifying conditional formatting
• Using the conditional formatting rules manager
• Creating conditional formatting based on a formula - Using Data Validation
• Using the data validation dialog box
• Creating data validation criteria
• Modifying and removing data validation
• Creating an input message
• Modifying the error message
• Creating a drop-down list for data entry
• Copying a drop-down list to multiple columns or rows - Creating PivotTables
• Create a simple pivot table
• Change the way data is calculated in a PivotTable
• Group date data by month, year, or quarter
• Learn about slicers – the new visual way to filter PivotTables
• Format a PivotTable report for greater appeal - Using Lookup Functions
• Learn the arguments in the VLOOKUP function
• Understand the importance of absolute references within many lookup functions
• Use VLOOKUP to perform approximate matches
• Simplify multiple-field look-ups with concatenation (combining fields into a single cell)
• Use VLOOKUP to look up data from another workbook
• Learn how VLOOKUP stops looking after it finds an initial match within a list
• Explore why VLOOKUP sometimes returns #N/A instead of the desired result
• Use the IFNA function to display something other than an #N/A error value when VLOOKUP can’t find a match
• See how the HLOOKUP function enables you to perform horizontal matches
• Learn why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
• Use the MATCH function to find the position of an item on a list
Course Level - Intermediate and Advanced
Who Should Attend
- Business Owners
- CEO's / CFO's / CTO's
- Managers
- Accountants
- CPA's
- Financial Consultants
- IT Professionals
- Auditors
- Human Resource Personnel
- Bookkeepers
- Marketers
- Anybody with large amounts of Data
- Anybody who uses Microsoft Excel on a regular basis, and wants to be more efficient and productive
Why Should You Attend
This training session covers the THREE features of Excel that provide automation within the application. Data is important. Locating specific data is important. Being sure that the correct data is being inputted is critical.
Thanks to Conditional Formatting, you can highlight all information that is important to you (the highest or lowest values, duplicate values, values that are larger or smaller than a certain value). To be sure that the data is entered accurately, use Data Validation to restrict the input type of a value based on a specific set of criteria. Trying to enter data that does not meet the criteria will generate an error message.
PivotTables make building reports a breeze. You will be introduced to the basic task of building, formatting, and refreshing PivotTables. Lookup functions are far superior to manually searching for specific data elements in a spreadsheet. VLOOKUP, HLOOKUP, INDEX, and MATCH are some of the most well-known (and most useful) functions in Excel.
-
$300.00
-
