Sunday, October 17, 2021
Excel's absolute cell references, like $A$1, confuse many Excel users. But the explanation is easy. Each dollar signs serv only one important purpose.

How to Use Absolute and Relative Cell References in Excel Formulas

A reader sent me this question: Could you send me more details about using $'s like the following: =AVERAGE($6:$6) averages all data in ROW 6....
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,...
You can return any number of items from an Excel database. Just use Data Validation to return one item and INDEX-MATCH to return the others.

Use Excel’s Data Validation Feature to Return Items from a List

Dawn has a list of people and their email addresses in her spreadsheet. She wants to select a name from a list and then...
The 'List' Feature of the Data Validation command offers a quick and easy way to set up a dropdown list box that you can use for choosing items in a cell.

How to Set Up a Data-Validation List Box in Excel

Often, we need to set up a list box in Excel, a sorted list that allows us to choose an item from an Excel...
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...
When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Here's the second of two methods.

How to Delete Blank or Unneeded Rows, Method 2

Data copied from a web site or imported from a variety of sources often include irrelevant rows of data. For example, the only way to...
In a workbook with many sheets, you easily can select the sheet you want from a list of sheet names in your workbook. And you don't need VBA to do it.

Quickly Find a Worksheet in an Excel Workbook With Many Sheets

I’m not opposed to using VBA. I think it’s great fun and it can be very useful. But in a business setting, if VBA...
In Word, we can set up custom forms, and then tab through them to enter our data. Here's how to tab through forms in your cells in your worksheets.

How to Tab From Cell to Cell in an Excel Worksheet Form

As a general rule, it's a good idea to find a way to import data from some other source, rather than entering the data...
When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Here's the first of two methods.

How to Delete Blank or Unneeded Rows, Method 1

When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Often, you’ll want...
Excel includes many outstanding features that you probably don't know even exist. Here's how to find those features and to use the ones you need.

How to Find and Use Excel’s Best Hidden Features

Excel has many outstanding hidden features. Here are a few that I use frequently… In Excel 2013 and above, when you close Excel's last...

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