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

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

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