Main content

Excel: Business Analytics


The Business Analytics field is popular and growing; Microsoft Excel has many features to make creating analytics easy, attractive, and powerful. Many positions require that we know how to manage, manipulate, and analyze our data table information. We well learn how to manage and manipulate our data and prepare the information for analysis. Then, we will work with Excels’ four important analytical tools: Scenario Manager, PivotTables, Subtotals, and Charts/PivotCharts.

Audience: We designed this course for current, experienced users of Excel who are comfortable using formatting, sorting, and filtering features.

Software: This course is designed for Microsoft Office 2016 and Microsoft 365.

After completing this course, participants will be able to:

  • Manage tables with the Name Box and shortcuts
  • Manipulate information with filtering and sorting
  • Use Format as Table for an attractive and more functional data table
  • Save and re-use What If analysis using Scenario Manager
  • Create and edit PivotTables and PivotCharts to summarize our data
  • Employ Subtotals and Grand Totals in our table
  • Construct various type of charts to represent our data graphically

Course Content


**DUE TO SOCIAL DISTANCING RECOMMENDATIONS, THIS COURSE WILL MEET ONLINE**

Lesson 1. Managing and Manipulating Information

  • Navigation and selection shortcuts and tips n tricks
  • Filter using multiple criteria for text, dates, and numbers
  • Sort by multiple columns
  • Productive and necessary text functions
  • Format as Table

Lesson 2. Name Box and Name Manager
  • Name cells, tables, and areas of a worksheet
  • Use Names for:
  • o Absolute Reference
  • o Navigation
  • o Selection
  • o Values
  • o Formulas and Functions

Lesson 3. Scenario Manager
  • Add and edit What If analysis on finances and forecasts for budget/expenses
  • Save scenarios and re-use
  • Create a summary worksheet of our scenarios

Lesson 4. PivotTables and PivotCharts
  • Insert a PivotTable to summarize a data table
  • Use SUM, AVERAGE, COUNT functions and percentages
  • Filter our PivotTables with Slicers
  • Build four or more PivotTables for HR, Finances, Sales, Operations/Administration, Marketing
  • Design our PivotTables with attractive styles and banded rows/columns
  • Pivot our data with rows, columns, values, and worksheets
  • Add calculations to items and fields

Lesson 5. Subtotals
  • Use subtotals to quickly create subtotals and grand totals within the data table
  • Employ SUM, AVERAGE, and COUNT functions for Subtotals
  • Insert multiple subtotals into one table

Lesson 6. Charts
  • Construct column charts with ease using shortcuts
  • Create pie, column, bar, line, waterfall, and many other types of charts
  • Edit charts and add captions, number formatting, and design elements
  • Format charts in many appealing ways
  • Filter charts