Thursday, February 22, 2024
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,...
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...

Latest Articles

Whoops! We’ve Got a Broken Home Page!

Sorry about that. We're working on it. Hope to get it fixed soon.
Excel Flowbook Revolution

Getting Radical with Excel

It's time to think about Excel in a radical new way—when we use it to work with business or economic data. It's time, in fact,...
Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.

How Fast Is Too Fast?

(Originally published in Inc Magazine.) What typically tops the list of worries of the chief executive officers of fast growing companies? Financing that growth, according...