Sunday, October 17, 2021
Excel databases can be Tables of data, Pivot Tables, or even lists that your reports and analyses can use easily. This introduction will get you started.

What the Heck is an Excel Database?

You can raise your Excel productivity significantly and slash your Excel errors—if you keep your data in an Excel database. Microsoft, Oracle, IBM, and others...
In a list of items that could appear in a worksheet any number of times, here's how to count the number of items that appear only once. Or twice. And so on.

How to Count the Occurrences of Items in a List

"I have a list of repair orders (RO). I want to know how many one line repair orders there are in the list. So...
The two Excel dashboard-like charts with May data show that consumer confidence has dropped like a rock since March. But Asia's data isn't available yet.

Dashboard-Like Excel Charts of International Consumer Confidence

The following Excel figure displays its charts in a dashboard-like format. At least, it's in a format that dashboards should use! When you use simple...
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...
Does your company need to make scheduled payments to companies that don't send invoices? This worksheet will help to plan those cash requirements.

Manage Periodic Payments with an Excel Cash Calendar

Most companies must write checks periodically to companies that don't send invoices. Often, their accounting systems provide little help in keeping track of these...
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...
In this final article of the SUMIFS, SUMPRODUCT series, you'll learn more reasons to use advanced multi-criteria lists in marketing and finance.

Advanced SUMIFS Calculations with Criteria Lists

In Part 1 of this series, you learned how to use criteria lists for marketing data. In Part 2, we looked at financial data. And now, in Part...
Both IPMT and ISPMT return the interest payment for a given period for a loan or investment. But Microsoft's help topics haven't made their differences clear. Here's what you need to know about these two financial functions.

How to Create Even-Payment and Straight-Line Amortization Tables in Excel

When you borrow money for a fixed period with periodic payments, you could have two types of loans: even-payment or straight-line. Microsoft recognizes those loan...
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...

Latest Articles

To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.

How to Foot and Cross-Foot Excel Reports in a Floating-Point World

At first glance, the following report is an ordinary one. It merely sums an Excel Table by Product and Region, and then foots and...

Excel’s XLOOKUP Function Explained

Excel's XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't...

Excel’s VLOOKUP Function Explained

Excel's VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell. VLOOKUP is...
Advertisement
Advertisement