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

Excel Strategies

How to Work Without Limits in Excel

An article in The Wall Street Journal says that companies are moving away from Excel because of its inability to work well with data.

This isn't a new problem. It was solved more than twenty years ago.

Formula Power

Advanced SUMIFS Calculations with Criteria Lists

IIn this final article of the SUMIFS series, you'll learn more reasons to use advanced multi-criteria lists in marketing and finance.

And you'll learn other options for using this powerful method.

Formula Power

How to Create Summarized Financial Statements with SUMIFS Criteria Lists

In one formula, you can summarize any number of specific accounts in a list, adding or subtracting them as you want.

This makes it easy to summarize financial data from an accounting trial balance.

Formula Power

How to Use SUMIFS with Criteria Lists, Summarizing Sales

The SUMIFS function returns a sum for nearly any number of single criteria.

But here's how you can return the sum for many criteria listed in a Criteria List.

Analytical Strategies

Beveridge-Chart Trend Analysis with Excel

The Beveridge ChartEconomic analysts use the Beveridge Curve to analyze the labor market through business cycles.

You can use it to analyze how any two measures—internal or external—correspond over time.

Rules of Thumb

Excel and the Rule of 72

The Rule of 72 is a guesstimate of how long it will take an investment at a specific interest rate to double in value.

But how accurate is this rule? This Excel analysis provides the answer.

Business Planning

How to Audit and Improve Excel Business Plans

Excel is a great tool for creating business plans. But you need to make them auditable and as accurate as possible.

One massive Excel business plan offers some great ideas about what NOT to do. 

Interactive Reporting

How to Use Slicers to Control Settings in Your Reports and Analyses

Interactive reporting with slicersSlicers normally are used with PivotTables and Excel Tables.

But you also can set them up to control settings, just as you would when you use Validation Lists.

This article explains how.


Interactive Reporting

Excel Interactive Magic with Slicers

Interactive line chart using a slicerHere's a great way to set up interactive figures in your dashboard reports.

Follow this link to see it in action.


New Chart Feature

Two Business Uses for Excel's New Chart Feature

Finally! Your formulas soon will be able to set gaps in your line and scatter charts. Here, for example, those gray highlights were generated by one XY (scatter) plot.

Click the image for more information about this great new feature.

An Excel Pointlink chart

Excel Productivity

The Excel-Friendly Database Strategy

For decades, Excel has been criticized for many problems: It takes too much work. It makes errors easy to generate. Different reports of the same data give different results. And so on.

Here’s the strategy that will allow your company to solve most of those productivity problems with Excel reporting and analysis.

Lookup Formulas

Tasks VLOOKUP Can't Do

INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster.

In this final article of the series, six examples of what VLOOKUP can't do will build your lookup toolkit.

(Includes free downloads of lookup examples.)

Excel Strategies

You REALLY Don’t Know What Excel Can Do!

Cutting-edge companies that understand Excel's real power have turned Excel and its users into a strategic resource.

Would you like to use Excel for data mining? Real-time planning and consolidation? Collaboration? Analysis of data from any available sources? Many companies do exactly that every single day.

Lookup Formulas

The MATCH Function

This is the second of a three-part series designed to INDEX-MATCH clearer.

The article explains all three types of the MATCH function, and why you'll use of these about 95% of the time.

(Includes free downloads of examples.)

Lookup Formulas

The INDEX Function

INDEX-MATCH is Excel's most-powerful lookup method. But many users find it challenging.

In this first of a three-part series, you'll learn about the powerful INDEX function.

(Includes free downloads of examples.)

Excel Alerts

Conditional Formatting with Formulas

Excel offers two types of conditional formatting: formattting a range based on the value of each cell, and formatting a range based on formulas. 

Here' how to use the second approach.

Professional-Quality Charts

The Basics of Professional Excel Charting

Step 8Your managers, clients, and other VIPs will pay more attention to charts that look professionally designed.

In this article, you'll learn the basics of how to give your charts that professional touch.

Excel's QAT

How to Find and Use Excel’s Best Hidden Features

Excel includes many outstanding hidden features. For example, in Excel 2013 and above, you easily can keep Excel open even when all workbooks are closed. You can close all workbooks with one command. And a lot more.

Here's how to find and use Excel's best hidden features easily.

Time-Value-of-Money Formulas

Help with Excel's Most Confusing Time-Value-of-Money Functions

These two time-value-of-money functions have caused much confusion over the years...largely because their Excel help topics have omitted some key information.

Here's the information that will help you to use these two functions easily.

Reporting Tools

How to Set Up Cascading List Boxes for Filtered Excel Reports

You can set up cascading list boxes so that your second list of items changes in response to the item you selected from your first list.

You could use a primary-secondary list structure like this to choose the names of people in the selected department, the products you buy from a selected vendor, and so on.

Reporting Strategies

Do Your Excel Reports Document Performance? Or Communicate It?

In your Excel reports and analyses, there's a huge difference between documenting performance and communicating it.

Do you know that difference?

Management Reporting

Five Best Practices in Your Excel Reporting Strategies

Your Excel reports and analyses often can abuse your managers. Here are five best practices to follow in planning your management reports.

Modeling and Simulation

How to Create Monte Carlo Models and Forecasts Using Excel Data Tables

The Monte Carlo method helps you to create statistically accurate forecasts. And Excel Data Tables allow you to create Monte Carlo forecasts without using macros. Here's how...

Monte Carlo Forecast

Modeling and Simulation

Introduction to Probabilistic Simulations in Excel

You can use probability distributions to manage the uncertainty about your assumptions when you create simulations in Excel. Here's how.

Excel chart of a normal curve with shaded areas.

Error Reduction

Seven Ways to Avoid Errors In Excel Reports & Analyses

You can find many stories on the web about problems with spreadsheet errors.

So here are some quick and easy ways to avoid errors in your Excel reports and analyses.

Report Distribution

Eight Tips to Make Excel Easier to Use

You can save any number of Excel reports to one PDF file using only Excel.

Here's how to do it...

Report Distribution

How to Save Multiple Excel Reports to One PDF File

You can save any number of Excel reports to one PDF file using only Excel.

Here's how to do it...

Successful Reporting

Five Ways to Make Your Excel Reports Incredibly Popular

Many Excel reports are a waste of time and money. But that's not the worst of it...

In many companies, Excel reporting turns Excel users into spreadsheet clerks, and wastes the incredible benefits that Excel users can bring to your organization.

Here's how to make your Excel reports both valuable and popular.

Analytical Reporting

Use Automated Cross Correlations in Excel to Find Leading Indicators, Part 2

Now that you've calculated your cross correlations—that is, your time-shifted correlations—here's how to chart them professionally.

Cross correlation analysis

Analytical Reporting

Use Automated Cross Correlations in Excel to Find Leading Indicators, Part 1

Leading indicators can help you to forecast more accurately. And cross correlations can help you identify leading indicators.

Here's how to calculate and display cross correlations in Excel, automatically. 

Cross correlation analysis

Financial Functions

Excel’s Five Annuity Functions

Most loans and many investments are annuities, which are payments made at fixed intervals over time.

Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Chart Techniques

Using Excel XY (Scatter) Charts for Stock Analysis with HLOC Plots

This Excel chart of High-Low-Open-Close (HLOC) stock data illustrates key techniques that you can use in your own charts...even if they have nothing to do with the stock market.

HLOC Display using Excel XY Chart 

Chart Techniques

Fun with XY (Scatter) Charts in Excel

Excel's XY (or "scatter") charts provide amazing power. This introduction to XY charts offers a fun way to learn more about this powerful chart type.

Excel Scatter (XY) Charts

Basic Excel

Compare Loan Payment Options in Excel

Loan payment calculatorMortgage lenders give you many options for your interest rate and amortization period.

This workbook helps you to choose from among them.

Basic Excel

Save Time with Excel Range Selections

One of the big differences between early-stage Excel users and gurus is that gurus can do simple things in Excel much more quickly than other users can.

They can do this because they understand how Excel works in situations that most Excel users experience frequently. Here are some examples...

Using Date Formulas

Nineteen Date Calculations in Excel

Nineteen date calculations in ExcelExcel reports and analyses tend to use similar types of date calculations.

This article explains nineteen of them, using DATE, EDATE, EOMONTH, and other functions.


Chart Techniques

Bad Chart! Bad, Bad Chart!

This chart figure from the Wall Street Journal offers lessons about what NOT to do with your own Excel charts and dashboards.

Example of a bad chart from the Wall Street Journal


Reporting Strategies

How to Create Funnel Charts in Excel

An Excel funnel chart

"Funnels" are widely used in business, but Funnel Charts aren't. Here's how to set up a Funnel Chart in Excel.

Download a Free Excel Dashboard

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.

Excel Forecasting

How to Create a Rolling Forecast of Seasonal Sales in Excel

Excel cycle plots of seasonal data

The extreme variability caused by seasonal sales makes it difficult to forecast them. 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.

Financial Planning

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.

Advancing Your Career

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

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.

Dynamic Reporting

Create Variable-Length, Dynamic Reports Linked to Excel Tables

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 Tables

Read and Update Excel Tables Using SUMIFS and INDEX-MATCH

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.

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.

Managing Excel Data

Introducing Excel’s Three Types of Spreadsheet 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.

Insight from charts

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

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

Managing Excel Data

How to Set Up a Pivot Table as a Spreadsheet Database

Pivot Tables aren't merely a way to interact with your data. You also can use them as a rich source of data for regular reports and analyses...without using the weak function, GETPIVOTDATA.

Excel Shortcuts

Five Really Useful Excel Keyboard Shortcuts

These keyboard shortcuts will help you to things in Excel that are difficult or time-consuming to do any other way.

Chart Reporting

US Economic-Overview Excel Dashboard Report for Managers

Here's an Excel dashboard showing current US Economic data from online sources. And it's based on a dynamic Excel dashboard template.

Management Charts

Introducing the Power of Year-Over-Year Performance Charts

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

Excel Charts

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

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

Chart Formatting

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

Remove jaggies in rotated text using one tip.

Excel Charts

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.

Advanced Lookups

How to Use SUMPRODUCT to Create Two-Dimensional Lookups

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