Sunday, June 20, 2021
Excel offers two powerful worksheet functions that can return the data you need from any type of worksheet database. One is more powerful, but calculates slowly. The other is nearly as powerful, but much faster.

The Two Functions You MUST Know to Return Values from Excel Tables

“Export to Excel is the 3rd most common button in BI apps…after OK and Cancel.” —Rob Collie, one of the founding engineers behind PowerPivot at...
You can sort data with formulas in two ways. One way requires Excel 2019 and above. This article explains how to sort data in all versions of Excel.

How to Sort Data in Reports Automatically Using Excel Formulas

Many Excel reports include tables that show sorted results. Usually, these tables were sorted manually in Excel, using the Data, Sort command. However, reports...
Here's how to add sources and uses of funds to your company's Balance Sheet in Excel to get a clearer picture of how the changes affect your cash flow.

Use this Excel ‘Cash Flow Balance Sheet’ to See Sources & Uses of Funds...

For most businesses, cash flow is more important than profits and losses. The reason is clear. A profitable company with negative cash flows may not...
To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.

How to Define General Ledger Account Groups in Excel

In Part 1 of this series, How to Report GL Account Groups in Excel, we explored the strategy for creating financial reports that use...
When you combine conditional formatting with charts you can create detailed chart legends that match the relative positions of the lines in your chart.

How to Create Dynamic Chart Legends in Excel

When I saw a chart designed like the ones below in the Wall Street Journal, I knew I had to create one just like...
Bullet graphs show the same information that dashboard gauges do, but they're smaller and easier to read. Here's how to create your own bullet graphs in Excel.

How to Create Bullet Graphs to Replace Gauges in Excel

Business presentations desperately need a replacement for dashboard gauges. Gauges consume too much space in a report. They use excessive "chart junk." They're not...
How to aggregate named groups of GL accounts.

How to Report GL Account Groups in Excel

Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...
These Excel dashboards, inspired by long-running displays in business magazines, illustrate more ways to format your Excel reports.

Sample Excel Dashboard Reports from Forbes and Business Week

This mock-up is based on a format that Business Week used about thirty years ago. However, their report used a different color scheme. As...
If you have seasonal sales, or other measures of performance, Cycle Plots can offer more insight into your performance than traditional charting techniques.

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

If your company's sales are seasonable, you've probably seen a chart that looks something like the first one below. This Excel chart shows the continuous...
Here's how to use formulas and conditional formatting to expand or contract your Excel report automatically, without macros, when you change a cell value.

Create Variable-Length, Dynamic Reports Linked to Excel Tables

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a...

Latest Articles

A Lesson in Excel Analysis Learned from Rising US Inflation

Looking at the trends in the annual rate of change (ROC) of business and economic measures is an excellent way to reveal hidden information...
Here's how you can split delimited text into an array with a short Excel formula, and then wrap the formula with the INDEX function to return any value from the array.

How to Split and Index Delimited Text with Excel Formulas

For the longest time I've wished that Microsoft would add a SPLIT function to Excel. The function would have a syntax like this... =SPLIT(text, delimiter,...
Berkshire Hathaway is raising prices, and their costs are increasing. Inflation is on the rise. These Excel charts illustrate the the problem.

Warren Buffett, Inflation, and Excel

On May 3, 2021, CNBC quoted Warren Buffett saying, “We are seeing very substantial inflation. We are raising prices. People are raising prices to...
Advertisement
Advertisement