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 Excel Tables, you can filter on any two conditions in a column. But by using the SUMPRODUCT function, you can filter on any number of items in a list.

How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily. However, the filter capability has...
In one SUMPRODUCT formula, you can summarize any number of specific accounts in a list. This makes it easy to summarize financial data from an accounting trial balance.

How to Create Summarized Financial Statements with SUMIFS Criteria Lists

In How to Use SUMIFS with Criteria Lists, Summarizing Sales, I explained how to use the SUMPRODUCT function with SUMIFS to return the sum of...
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...
Excel offers at least three ways to set up data so your reports and analyses can use it easily as a reliable data source.

Introducing Excel’s Three Types of Spreadsheet Databases

Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas: Simple...
Here's how select a value from a list of values from a worksheet's a Validation List. You can use the list to display interactive data in reports.

How to Select Values from a Validation List in Excel

Excel provides two ways to select a value from a list of values. I often use this feature in interactive Excel reports to select dates,...
Do you need to update your Excel reports with daily, weekly, or monthly data? Here's a low-maintenance way to do it, using one type of Excel database.

How to Report Periodic Data from Excel Databases

Sometimes, the best way to solve your Excel problem is to redesign your workbook. Ron G. brought this thought to mind with a recent question....
Two unusual formulas can use the SEARCH or FIND functions to search any number of cells for any number of words—almost instantly.

How I Set Up SEARCH and FIND Formulas on Steroids

I've used Excel long enough to recognize when I've stumbled across an unusual formula. And it happened again a few minutes ago. In the past,...
You can set up cascading list boxes so that your second list of items changes depending on which item you selected from the first list. Here's how to do it.

How to Set Up Cascading List Boxes for Filtered Excel Reports

When each item in a primary list is associated with a different collection of items in a group of secondary lists, you can use...
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

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