Hour 1: Productivity Tips and Tricks: Excel comes with “Some Assembly Required". You will setup and learn numerous features, buttons and tools.
Hour 2: Tools of the Trade and Every Day Excel Hacks: These are the old-school tips and tricks that have been forgotten by so many Excel users.
Hour 3: Useful Formulas: learn a handful of the most useful and often used formulas in Excel. These will include: VLookup, HLookup, IF Error, Text to Columns, Concatenate, and the power of the Name Box when creating formulas.
Hour 4: Power Query Intro: When the redundant task of 'cleaning up this week's data' comes across your desk, Power Query is the answer. It handles the repetitious steps specifically tied to data prep.
Hour 5: Pivot Tables Crash Course: This is all about data summaries and data analysis. You will meet the Pivot Table window, learn the design process, the advanced level features (dashboard slicers), and have access to a downloadable PDF guide with step-by-step instructions on each feature covered.
Hour 6: Data Visualization Crash Course: "Data Viz" is the visual transformation of a block of data and a simple chart, to an actual message, delivered in a “publish worth” format. The focus is on the message of the chart, and the presentation of the data.
Why you should Attend
Are you a self taught Excel user, and you like to mix-it-up and venture into advanced features and formulas, occasionally getting stuck along the way?
This seminar is perfect for you! We will start by formalizing your foundation of Excel, then move onto some intermediate and advanced Excel functions that will bring you up to speed.
You'll be learning from a 25+ year Excel Certified Expert, who shares amazing tips and tricks from beginner to advanced.
Areas Covered in the Session
- Hour 1:
- Set up your custom Quick Access toolbar
- Apply the 5 Super Secrets to Software
- Understand the most common Excel formulas
- Create a two-step chart
- Control page layouts, columns, rows, and margins
- Hour 2:
- Split Screen
- New Window
- Watch Window
- Auto Calculate Feature
- Transpose Data
- Remove Duplicates
- Conditional Formatting
- Split Text to two or more Columns
- Combine Text from Two Columns to One
- Hour 3:
- Protect a Worksheet or Workbook from RUIN!
- Use two types of IF Functions (Logical and Error)
- Name Box - Advanced level
- Understand the syntax of the VLookup and HLookup formulas
- Absolute Ref vs Named Range in Formulas
- Formula Auditing
- Hour 4:
- How to begin a Power Query
- How to Extract data from your Source files into the Power Query
- How to Transform the data (clean it up)
- View a few examples of the Transform process
- Learn the power of the Query Settings and the Advanced Editor
- How to Load the data and Save the Excel file
- And finally, how to re-open and edit the Power Query Settings
- Hour 5:
- Quickly summarize data with Pivot Tables
- Manipulate (or Pivot) the Pivot Table for varying results
- Group data by custom number ranges and data ranges
- Create a calculated field within the Pivot table
- Learn the built in “Summarize By…” features
- Learn how to “drill down” to details
- Learn how to keep your Pivot table spreadsheet tidy
- Learn why named ranges are a benefit
- Create Dashboard tools to assist others who might use the workbook
- Learn the vital Pivot Table settings that will save time and frustration
- Hour 6:
- Theory of Patterns in Data Presentation
- Chart Tools Ribbon
- Charting Tips, Tricks, Short Cuts
- Chart Elements and Color
- “Save As Template”
- Determine the appropriate chart type for your data
- Manipulate the chart for visual clarity
- Review and technically apply data presentation techniques
- Review tools that assist instead of a full chart: Conditional formats and Sparklines
Who Will Benefit
- Financial consultants
- Human Resource
- Logistics & Supply Chain
- Medical Devices
Andy Lanning, owner, trainer, and Chief Nerd of Computer Software Training, LLC.
Andy has 25+ years of experience teaching computer software in the corporate and university environments. She teaches basic to advanced levels in most Microsoft Office programs. She is adjunct faculty at Boise State University, since 1994 to present, where she specializes in computer training for the Center for Professional Development. She also travels to teach at company conferences across the U.S.
Andy’s background includes corporate training, private training, and freelance data analysis. She has recorded several online training courses for Udemy.com, GoSkills.com, and in her career has delivered hundreds of courses and webinars on a variety of Microsoft topics.