Sunday, June 20, 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

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