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

Analytical Charting

How to Show Recessions in Excel Charts

Chart of Apple's revenues with recessionsWhen you include recessions and economic slowdowns in your charts, you can show your company's performance in a much better context.

Here's how to set up your Excel charts to display those recessions and slowdowns.

Analytical Charting

Chart Your Rate of Change to Reveal Hidden Business Performance

Apple's rate of change of revenuesBy charting the annual Rate Of Change (ROC) of key measures, you can uncover significant information about your time-series data.

And by using the right recession measure, you can put that information into better context.

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.)

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.

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...

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

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.

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.


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

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 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.

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.


Free Excel Dashboards

Charley's SwipeFile charts