For business users of Microsoft Excel Free guides and templates
Home > 

Advanced Lookup Formulas

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

Multi-Criteria Lookups in ExcelHow do you write a formula that returns the Manager's name from this Excel Table for a specified Date, Region, and Product?

Here are two ways to do it...

How to Use Advanced Filters to Create a Unique List of Items from an Excel Table

How to Set Up a Data-Validation List Box in Excel

Business Insight

Five Ways to Improve Business Insight in Your Excel Reports

Excel dashboard with the worst trends
An article in the Harvard Business Review discussed seven places to look for insight in business. Five of the ideas can apply to management reporting with Excel dashboards.

Interactive Reporting

How to Set Up a Data-Validation List Box in Excel

Excel's Validation ListThe 'List' Feature of Excel's Data Validation command offers a quick and easy way to set up a dropdown list box.  Here, for example, you can choose either North or South.

Interactive Reporting

How to Use Advanced Filters to Create a Unique List of Items from an Excel Table

Advanced filterExcel's Advanced Filter command provides a quick way to generate a unique list of items found in an Excel Table. Here's how to generate the list.

Excel Growth Rates

How to Calculate BOTH Types of Compound Growth Rates in Excel

If you search the web to learn how to calculate a growth rate in Excel, you’ll likely learn about only one type of rate. That’s too bad, because in business, we often need to calculate at least TWO types of growth rates.

Advancing Your Career

How to Disguise Your Company-Private Excel Dashboards So You Can Show Them to Others

Original Excel dashboard.
An Excel dashboard report would be a great addition to your resume. But using your Excel dashboard reports like this is difficult because they typically display company-private information. So here's how to solve this problem.

Managing Data

Use a 'Visible' Column in Formulas to Ignore Hidden Rows in Filtered Tables

Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.

Excel Tables

Read and Update Excel Tables Using SUMIFS and INDEX-MATCH

Use SUMIFS and INDEX-MATCH to transfer data between Excel's two most-widely used types of tables.

With a few Excel formulas, you can move data between Excel's two most widely used table designs...and automatically transfer data from your source files into a more useable form.

Excel Forecasting

How to Create a Rolling Forecast of Seasonal Sales in Excel

Excel chart showing a forecast of seasonal sales

The extreme variability caused by seasonal sales makes it difficult to forecast them. In fact, it's hard to picture any method that could generate a reliable sales forecast that has a pattern like the one shown here. But there's no problem if you know how to generate a seasonal index.

Charts for Managers

How to Create Cycle Plots in Excel to Chart Seasonal Sales Data

An Excel cycle-plot chart.

If you have seasonal sales, or other seasonal measures of performance, Cycle Plots can offer greater insight into your performance than traditional charting techniques.

Dynamic Reporting

Create Variable-Length, Dynamic Reports Linked to Excel Tables

Variable-length Excel report.

When you change a criteria value in a cell, Excel can use formulas and conditional formatting to expand or contract your report automatically, without macros. Here's how.

Excel Databases

Map Your Financial Health with an Excel DuPont Dashboard

DuPont-Chart Dashboard in Excel

Management reporting is all about communication. Reporting Return On Equity (ROE) is a case in point.

The ROE financial ratio is a key measure of financial health. But to non-financial managers, the ROE can be difficult to understand, for two reasons...

Chart Techniques

Highlight Normal Results in Line Charts to Make Exceptional Results Stand Out

A formatted line chart with a path defined by the standard error of the estimate.If you highlight the standard error on each side of the trend, you'll help to make exceptional results in a line chart stand out.

To calculate the standard error you use the STEYX worksheet function.

Introducing Excel’s Three Types of Spreadsheet Databases

An Excel Table, on of three types of Excel databases.

Excel offers three ways to arrange data so you can use it as a database with your worksheet formulas: gray-cell database, Excel Tables, and Pivot Tables. Here's an introduction to all three.

Excel Charts, Seasonality, & Analysis: Five Lessons from the WSJ

Seasonal charts showing that women who give birth during the winter months tend to have fewer means. Source: The Wall Street Journal

An article in the Wall Street Journal about winter babies offers some great advice about charts for Excel users in business.

How to Set Up a Pivot Table as a Spreadsheet Database

Pivot Table as a Spreadsheet Database

Excel 2010 added the ability to create tabular PivotTables, which can be used as databases.

Five Really Useful Excel Keyboard Shortcuts

Check that the cell reference is correct

To check OFFSET, copy the reference then use a shortcut to launch the GoTo dialog.

Introducing the Power of Year-Over-Year Performance Charts

An Excel Year-Over-Year Performance Chart

Year-Over-Year charts show trends that other charts miss, and avoid seasonality issues.

US Economic-Overview Excel Dashboard Report for Managers

Top section of the US Economic Overview Excel Dashboard

The top section of the US Economic-Overview Excel Dashboard.

Follow Recessions by Country with an Excel Chart That's Not a Chart

An Excel chart that's not an Excel chart

This chart shows recession by country, using public data. But the chart isn't a typical Excel chart.

Three Tips that Help Readers Interpret Chart Trends Correctly, Using High-Quality, Dynamic Text

Remove jaggies in rotated text using one tip.

How to Create Normal Curves with Shaded Areas in New Excel

Even if you don't care much about statistics, you might find these techniques useful when you create other charts.

How to Use SUMPRODUCT to Create Two-Dimensional Lookups

A formula can look up both 'Mar' and 2010 to return a value from an Excel database.

Here's how to use SUMPRODUCT to find the circled number in the block of numbers and then return its row and column headings.

Worksheet Functions

What's a 'Deprecated' Function in Excel?

Microsoft tells us that many worksheet functions are 'deprecated.' So what does that mean, exactly? And specifically, just which functions have been deprecated? My short, Excel Help guide What's a 'Deprecated' Function in Excel?, explains deprecation and lists both the old and new Excel functions.

Charts & Conditional Formats

How to Create Dynamic Chart Legends in Excel

Dynamic Excel chart legends change as your data changes. One way to set them up is to use conditional formatting in your spreadsheet.

Improve Your Excel Lookup Formulas

Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

Excel’s VLOOKUP function is about 30 times more popular than the INDEX-MATCH function. This is too bad, because INDEX-MATCH is faster and more-powerful.

Management Reporting with Charts

Explore Business Performance Using Small Charts in Excel

When you compare measures of performance to other measures, you add context that can offer significant insight. We use data about public companies to illustrate the process.

Management Reporting

Ten Ways to Improve Performance with Excel Formulas Linked to the Web

All the economic and external business data your managers need is available on the web, probably for free. And you can use spreadsheet formulas to include that data in your reports, analyses, and forecasts.

Management Reporting

How to Report Your Company’s Private Rate of Inflation in Excel

There’s a strong likelihood that inflation will sky-rocket when the economy starts to improve. So it's a good idea to keep a close watch on the costs that affect your company.

Management Reporting

Use Free Online Data to Track Public Confidence Daily

When you’re planning for your company’s future, here's how to get an accurate and current picture of how optimistic or pessimistic the public is about the economy.

Excel Charts

How to Analyze Seasonal Sales in Excel

The extreme variability caused by seasonal sales makes it difficult to monitor and forecast your underlying sales trends. You can reveal those trends when you deseasonalize your sales data.

Excel Charts

How to Set Up Traffic Lighting in Excel

Wouldn't it be useful if your reports could highlight exceptional performance automatically? That is, wouldn't traffic lighting be useful? In fact, Excel provides outstanding traffic-lighting power.

Excel Formulas

Excel’s VLOOKUP vs INDEX-MATCH Functions

Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Although the two methods are similar, INDEX-MATCH is more powerful. Here's why.

Excel Formulas

Use SUMPRODUCT to Find the Last Item in an Excel List

When you have a list with repeated items in Excel, here's how to find information about the last occurrence of any item in the list.

Reporting Strategies

Which Offers More Business Insight? Dashboards? Or Normal Excel Reports?

The purpose of any report in business is to give its readers the most useful information in the shortest time possible. Which type of report best achieves this purpose?

Financial Reports

Add Cash Flow Information to Your Excel Balance Sheets

A simple change to your balance sheet created in Excel can provide key information to help you manage your cash flow more effectively.

Financial Management

Control Your Cost Lag Loop to Help Survive the Recession

The way you think about your expenses could determine how you'll fare in this economic downturn.

Financial Management

Predict Business Bankruptcy Using Z Scores with Excel

Here's a simple analytical method that can help you to predict whether a business is headed for bankruptcy.

Spreadsheet History

Dashboards Before Excel: Mini-Graph Reports in 1-2-3

Spreadsheet dashboard reports have been used for much longer than you might think. Here's an example from the early days of spreadsheets.

Excel Charts

Create Cycle Plots in Excel to Chart Seasonal Sales Data

If you have seasonal sales or other measures of performance, Cycle Plots can offer greater insight into your performance than traditional charting techniques.

Reporting Strategies

Five Reasons Excel BI Is Key to Your Company's Success

Although commercial Business Intelligence software offers many benefits, Excel BI has those products beat in at least five key ways.

Financial Management

Use Excel to Fix Your Broken Accounts Receivable Measure

If you track Accounts Receivable the way most companies do, you probably know less about your receivables than you think. There's a better way, which also can improve your cash flow forecasts.

Excel Charts

Should You Raise Prices? Or Should You Lower Them?

If you raise prices, sales could fall, bringing less profit than before. If you cut prices, sales must increase to compensate for your smaller profit margins. What should you do? This Excel chart can help.

Excel Databases

How to Return Items from a List Using Excel's Data Validation Feature

You easily can return any number of items from an Excel database. Just use Excel Data Validation to return the item you choose, and INDEX-MATCH to return the other items.

Excel Reporting

How to Report Data Each Period from an Excel Database

Do you need to update your Excel reports with daily, weekly, or monthly data? Here's a low-maintenance way to do it.

Reporting Strategies

Pablum Dashboard Examples

Too many dashboards are designed to spoon-feed their readers just a few facts at a time. Here are a few examples.

Budgeting & Forecasting

Use Benford's Law with Excel to Improve Business Planning

Benford's Law addresses an amazing characteristic of data. Not only does his formula help to identify fraud, it could help you to improve your budgets and forecasts.

Excel Formulas

Use Excel's SUMPRODUCT to Summarize Worksheet Data

Excel's SUMPRODUCT function offers much of the power of Excel array formulas, but without the complications.

Excel Summarizing Formulas

How to Summarize Excel Data for Reporting and Analysis

Excel offers several ways to summarize data quickly and easily. The most powerful and flexible approaches typically include using Excel arrays.

Excel Financial Formulas

Find Future & Present Values from Scheduled Cash Flows

Here's how to calculate Future Values and Present Values from cash flows that aren't necessarily periodic.


Free Excel Dashboards

Charley's SwipeFile charts