Microsoft Excel Blog: Tutorials and How-Tos

Calculate weekly revenue using PivotTables or Vlookup

Need to calculate weekly revenue or orders in Excel? You either Vlookup or PivotTables. PivotTables have a grouping features that will calculate it.

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.

Correct data imported into Excel from a database

When you import data into Excel from a database, often dates and numbers will import as text. The issue is you can't analyze the data.

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.

Create a break even analysis chart in Excel by Chris Menard

Break Even is when you making neither a profit nor a loss. This was a question from one of my MBA students. We will use the contribution margin

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.

Conditional Formatting with two criteria using AND function in Excel

If you need to select an entire row using two conditions with Conditional Formatting in Excel, you need to write a formula using the AND function.

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.

Use absolute reference in Excel to keep a cell constant when you copy or autofill

If you need a cell to stay constant, you can make it an absolute reference cell to stay constant. Press the F4 function key to create an absolute reference.