Intermediate Microsoft Excel tutorials

Vertical analysis of an Income Statement in Excel

To perform vertical analysis of an income statement in Excel, take all line items and divide by the Sales. One of my MBA students asked this question.

Five examples of using named ranges in Excel

You can use named ranges in formulas in Excel, to create a bookmark to a worksheet, and use for data validation in Excel. Chris Menard shows five examples.

Import Contacts from Excel into Outlook / People

Need to import your Excel file into Outlook People / Contacts? First, make your Excel file a CSV file. Next, make sure your data is correct.

Fix missing Excel data using Go To and Ctrl + Enter by Chris Menard

If you download Excel data from a database or a csv file, occasionally critical missing data will be missing. You can fix it with Go To and Ctrl + Enter.

Use the text function in Excel to add leading zeros

When you type in numbers in Excel, the leading zero will get dropped. Use the Text function in Excel to add back the leading zeros.

Create a data validation drop down list in Excel by Chris Menard

You can make a drop down list so only correct data is entered. If bad data is entered, sorting, filtering, pivottables, and other stuff will be incorrect.

Create your own custom sort in Excel

Excel already has built-in custom lists for sorting, but you can create your own custom sort list in case you don't want to sort alphabetically or A to Z.

Create a combo or two-axis chart in Excel 2016 by Chris Menard

If you are trying to chart values in Excel that are far apart numerically, you need to create a combo chart in Excel. It is very easy in Excel 2016.

How to calculate the weighted average in Excel

Need to calculate the weighted average in Excel? It is very easy if you use the sumproduct function in Excel. This is useful for school or work.

Create a treemap in Excel to spot patterns

A treemap chart provides a hierarchical view of your data and makes it easy to spot patterns, such as which items are a store's best sellers.

Use Sparklines in Microsoft Excel

Sparklines in Excel provide a fast way to see patterns at a glance. There are three type of sparklines in Excel: Win/Loss, Column, and Line.

Excel - make accounting, currency, and number style zero decimal places by default

Use the Control Panel to change the default decimal places to zero for accounting, currency, and numbers in Microsoft Excel. Video by Chris Menard