Excel for Business Analysts

Explore the golden rules when it comes to designing and organizing spreadsheets effectively.

Explore how to standardize file, tab, table and chart naming conventions as well as how to implement a basic version control system.

Create a summary sheet for the workbook to let others know how the spreadsheet should be used and managed.

Learn how to use color and pre-defined cell styles to add structure to the spreadsheet.

Explore some of the methods available in Excel to cut down on the amount of input errors and protect important formulas.

Data Validation helps us control what information is going into the spreadsheet.

Learn how to apply protection at the workbook, worksheet, and cell level to prevent unwanted changes.

Create a Data Validation List and use it to perform a flexible lookup using INDEX and MATCH.

Utilize INDEX and MATCH or XLOOKUP (Microsoft 365, 2019, 2021) to perform a lookup with two criteria.

Learn how to compare two lists and highlight differences using formatting.

Use the UNIQUE function to return duplicate values from two lists.

Create a new conditional formatting rule using a formula that highlights the row if a duplicate is found.

Create multiple data validation drop-down lists that change depending on the selection in the previous list.

Create interactive, dynamic checkboxes that harness the power of form controls and conditional formatting.

Learn the different ways Flash Fill can be used to combine, split and change items in Excel lists.

Learn the quick and easy way to import data from the web.

Explore how the CONCAT and TEXTJOIN functions can be used to combine data that's split across multiple columns.

Explore the numerous methods in Excel for splitting data across multiple columns.

Tidy up data by removing erroneous spaces, random line-breaks and weird non-printing characters by combining 3 useful Excel text functions.

Take a first look at Excel's new set of functions for manipulating text. (Microsoft 365, Excel 2021 only)

Take a first look at Excel's new set of functions for manipulating arrays. (Microsoft 365, Excel 2021 only)

A quick recap on the basics of Pivot Tables.

Explore advanced Pivot Table Tips and Tricks to supercharge your analysis skills.

More advanced PivotTable Tips and Tricks to supercharge your analysis skills. 

Got multiple datasets stored in different worksheets or workbooks that you want to analyze in one Pivot Table? If so, Power Pivot is your savior.

Let's continue our look at Power Pivot and how to combine multiple data sources.

Understand that not all charts are created equal and select the most appropriate chart type for your data.

Learn how to create a gradient map chart using Pivot Table data.

Create a histogram chart to display chart data grouped into bins.

Learn some advanced chart formatting techniques that tell the visual story of your data in a modern and effective way.

Use SmartArt to quickly create flow charts and process diagrams.

Represent values and trends in a cell using Sparklines and Data Bars.

Create in-cell visualizations using the REPT function.

Understand what a dashboard is and how you should prepare before creating a dashboard.

Create multiple Pivot Tables and Pivot Charts to display key metrics that are of important to your audience.

Continue learning about how to use Pivot Tables and Pivot Charts to display key metrics.

Create a calculations worksheet and use formulas to extract dashboard information from the source data.

Use formatting techniques to design a branded, consistent looking dashboard.

Bring all charts and calculations together on the dashboard and understand how to keep the dashboard updated when new data is added to the source.

Understand how forecasting works and how to forecast data into the future using Excel functions.

Learn how to create a seasonal forecast using forecast functions.

Calculate the upper and lower confidence bound. 

Create simple and quick forecasts using the Forecast Sheets utility.

Start with a target value and work backwards to find what needs to change to achieve your goal.

Use Scenario Manager to see what your data will look like if conditions change.

Use Data Tables predict values based on a set of conditions.

Course wrap up, review and goodbyes.