Tuesday, April 13, 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...
You can treat text files as relational tables. You can join them by common fields. Query them using SQL. And use the queries with PivotTables. Here's how.

Use MS Query with Text Files for Dynamic Excel Reporting

(Charley's Note: This article is very out of date. I'll update it as quickly as I can.) In the article, Use MS Query to Treat...
Excel ranges can work like relational tables. You can join them by common fields. Query them with SQL. And use queries in PivotTables. Here's how.

Use MS Query to Treat Excel as a Relational Data Source

Charley's Note: Because MS Query hasn't changed much since Marty Ryerson wrote this article years ago, his instructions are still quite accurate. I have,...
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...
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....
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...
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,...
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...
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...

Latest Articles

Using Excel's LET, SORTBY, and SEQUENCE functions, and dynamic arrays, you easily can list any number of top and bottom results from a Table.

How to Report Top and Bottom Results Using Dynamic Arrays in Excel

In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...
The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

How to Use Excel’s LET Function

In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

How to Set Up an Automatic Error-Checking System in Excel Reports

Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
Advertisement
Advertisement