ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd
Home > 

Excel Formulas, Data, Tables, & VBA

Ways to use Excel formulas and data, including worksheet functions, range names, cell references, databases, lookup methods, PivotTables, arrays, and VBA.

For reporting, particularly for financial reporting, see Excel Reporting and Financial Topics. And for detailed help about worksheet functions, see All Excel Worksheet Functions, with Links to Help Topics.

Managing Data

Use a 'Visible' Column in Formulas to Ignore Hidden Rows in Filtered Tables

Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.

Excel Tables

Read and Update Excel Tables Using SUMIFS and INDEX-MATCH

With a few Excel formulas, you can move data between Excel's two most widely used table designs...and automatically transfer data from your source files  into a more useable form.

Use SUMIFS and INDEX-MATCH to transfer data between Excel's two most-widely used types of tables.

Worksheet Functions

What's a 'Deprecated' Function in Excel?

Microsoft tells us that many worksheet functions are 'deprecated.' So what does that mean, exactly? And specifically, just which functions have been deprecated?

Together, Excel 2010 and 2013 list a total of 38 deprecated functions. Because several of them were replaced with two functions, those 38 outgoing functions have been replaced with 51 incoming functions.

My short, Excel Help guide What's a 'Deprecated' Function in Excel?, explains deprecation and lists both the old and new functions.

Managing Excel Data

How to Add Advanced Filter Capabilities to Excel Tables

When you filter columns in Excel Tables, you'll find yourself doing a LOT more clicking than you want. And you only can filter two criteria per column. Here's how to solve both problems.

Managing Excel Data

Introducing Excel’s Three Types Of Spreadsheet Databases

 

Excel’s Three Types of Spreadsheet Databases

Excel offers three ways to arrange data so you can use it as a database with your worksheet formulas: Simple Tables, Excel Tables, and Pivot Tables. Here's an introduction to all three.

Summary Reporting

Use COUNTIFS, not FREQUENCY, to Calculate Frequency Distribution Tables for Charting Histograms

 Charley's Swipe File Chart #58

Excel's FREQUENCY function was first created to calculate frequency distribution tables, which are needed for charting histograms. But the COUNTIFS function offers more power, and it's easier to use.

Excel Data Management

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

A simple database and a Data Validation area.

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.

Excel Lookup Functions

VLOOKUP vs INDEX-MATCH...Which Lookup Method is Better?

INDEX-MATCH is Excel's most-powerful and most-flexible lookup function.

Most Excel users need to look up data in workbooks. But what's the best lookup method? This article explains VLOOKUP, HLOOKUP, and INDEX-MATCH, and compares the two "lookup" functions with INDEX-MATCH. The winner should come as no surprise.

Excel Lookup Functions

Why INDEX-MATCH Is Better Than VLOOKUP or HLOOKUP in Excel

Rather than using VLOOKUP or HLOOKUP, you'll get more power and flexibility if you use INDEX-MATCH.

 Excel’s VLOOKUP function seems to be more popular than the INDEX-MATCH function. This is too bad, because INDEX-MATCH offers more power and flexibility.

Summarize Spreadsheet Data with Excel's Array Formulas

Excel array formulas rely rows and columns of data temporarily set up in Excel's memory.

Because array formulas can calculate using intermediate results in temporary memory, they may be Excel's most powerful feature for summarizing data. Here's how to use this hidden power.

How to Fight Spreadsheet Hell with Three Excel Lookup Functions

Spreadsheet Hell forces Excel users to work much longer and harder than they need to.

Do you spend hours each period turning raw data into useful information? You can fight this type of Spreadsheet Hell with help from three Excel lookup functions.

How to Set Up a Pivot Table as a Spreadsheet Database

PivotTable linked to an Excel database

You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables. Here's how.

Use SUMPRODUCT to Find the Last Item in an Excel List

Use SUMPRODUCT to find the last item in a list

In this Sequential List of items, how would you show the first and last dates for each item in the list? With SUMPRODUCT, of course!

Use Excel's SUMPRODUCT Function to Summarize Worksheet Data

A simple database for use with SUMPRODUCT

SUMPRODUCT works somewhat like array formulas, but without the complications. Unfortunately, Excel's help topic ignores the real power of this function.

The Most Powerful and Flexible Ways to Summarize Excel Data for Reporting and Analysis

When you use Excel arrays, perhaps your boss will be as kind.

How to Create Two-Dimensional Lookups in Excel Formulas

A formula can look up both 'Mar' and 2010 to return a value from an Excel database.

Two-dimensional lookups are easy in Excel.

Five Ways to Calculate Frequency Distributions in Excel

You can calculate frequency distributions with Excel's SUM-IF, FREQUENCY, SUMPRODUCT, INDEX-FREQUENCY, and COUNTIFS functions.

Excel Shortcuts

Five Really Useful Excel Keyboard Shortcuts

You'll probably use these little-known shortcuts daily. Suppose, for example, that you quickly want to read the help topic for a function—like MATCH. You type the first part of the function as a formula and then you use a shortcut.

Excel Statistics Functions

An Introduction to Excel's Normal Distribution Functions

A normal curve in an Excel chart

Excel provides several statistics functions for working with normal distributions. Here's an introduction to these functions

Excel VBA

Read a Text File with VBA, and Write the Text to Excel

VBA can easily read a CSV file and write the data to Excel.

Here's some heavily commented VBA code that reads a CSV file and writes it to a worksheet. The workbook includes cells where you can specify the name and path of the source file.

Excel Range Names

How to Create and Use Dynamic Range Names in Excel

Dynamic range names can give your spreadsheets greater power and flexibiity.

Dynamic range names in Excel are easy to use and give your formulas more power. Here's how to set them up in your spreadsheet.

How to Fix Excel's IRR Function

The Internal Rate of Return (IRR) calculation has very real theoretical problems, which Excel can't change. But Excel does offer a practical alternative that avoids the IRR's problems.

How to Work with Dates Before 1900 in Excel

A free VBA macro converts the original date text to a useable format.

If you work with dates prior to 1900, Excel offers little direct help. However, some Excel formulas and a free macro can fix these problems.

Excel Financial Functions

How to Use Excel Formulas to Calculate Term Loan Info

Term loans use a different amortizing method than traditional amortizing loans. Here's how to calculate amortization schedules for both term loans and traditional amortizing loans.

Excel Databases

Use MS Query to Treat Excel as a Relational Data Source

You can maintain ranges in Excel that work like relational tables. You can join them by their common fields. Query them using SQL. And use the queries with PivotTables. Here's how.

Excel Databases

Use MS Query with Text Files for Dynamic Excel Reporting

You can treat text files in a folder as relational tables in a database. You can join them by their common fields. Query them using SQL. And use the queries with PivotTables. Here's how.

Excel Databases

How to Perform Multiple Searches Using One SEARCH Function in Excel

Suppose you have two columns of text in your spreadsheet. The long column has cells that contain one or more tag codes; the short column contains only the tags that interest you. You can enter a SUMPRODUCT formula next to each cell of the long column that returns TRUE if the adjacent cell contains any of the tags in your short list. That is, each SUMPRODUCT formula can search its adjacent cell for each of the codes in your short list.

Excel Functions

Debugging Excel's INDEX() Function

When you use the INDEX function, here's how to test that it's giving you the data you expect.

Excel Summary Formulas

How to Summarize Excel Data for Reporting and Analysis

Excel offers several ways to summarize data quickly and easily. Here are the most powerful and flexible approaches, which include using Excel arrays.

Excel Databases

How to Return Items from a List Using Excel's Data Validation Feature

You easily can return any number of items from an Excel database. Just use Excel Data Validation to return the item you choose, and INDEX-MATCH to return the other items.

Excel Databases

Deleting Rows with AutoFilter

You don't need to write a macro to delete rows or columns with specific data from a database. Use AutoFilter instead.

Excel Databases

Use a Combo Box with Many Criteria in an Excel Database

Suppose you've got an Excel database with many fields, and you want to find certain records using multiple criteria. Here's how to do it.

Excel Web Data

Use Excel's Web Queries to Easily View Web Data

Excel's Web Query tool provides a quick and easy way to import web data to Excel.

Excel Web Data

Excel’s VLOOKUP vs INDEX-MATCH Functions

Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Here's an introduction to VLOOKUP and an illustration of one of the few times you should use an approximate match.

Excel Names & References

Using Absolute and Relative References in Excel Formulas

A visitor is confused about when to use "$" in cell references. Here's what every Excel user should know.

Range Names & References

Introducing Excel Range Names

Excel's range names offer more power than you might think. And they're not hard to use. Here's an introduction to the power that Excel provides.

Excel Date-Times

How to Round Time in Excel to Any Increment

With the help of three functions most Excel users seldom use -- INT, MOD, and CEILING -- you can round Excel time values to any increment you want.

Excel VBA

Corporate VBA Standards for Excel Users Who Program

Learn four VBA documentation standards that make your VBA code easier for you and others to understand.

Excel Text Functions

Excel’s CLEAN Function is More Powerful Than You Think

If you believe Excel's documentation about the CLEAN function, you'll get a false impression about CLEAN's power. It actually works better than advertised!

Excel Date & TEXT Functions

An Excel Date Conversion Formula

A reader downloaded a database with dates in an unusual format. That's seldom a problem in Excel if you know how to use its date and text functions.

Excel VBA

Use VBA Functions to Return Excel Document Properties

A reader wants his spreadsheet automatically to display information that spreadsheet functions don't return. That's not a problem with these two short User Defined Functions (UDFs).

Excel Date & TEXT Functions

How to Convert Unusual Date Text in Excel

When you import data into Excel, dates often arrive in formats that Excel doesn’t recognize. This is particularly true if your data comes from another country. Here's a general approach to solving this problem.

Excel Error-Reduction

Three Ways to Reduce Errors in Your Excel SUM Formulas

Simple spreadsheet edits can introduce costly errors. Here are three techniques that can help you to reduce such problems.

Excel Math Functions

How to Use Excel's Better-Known Rounding Functions: INT, ROUNDUP, ROUNDDOWN, & ROUND

Excel's four better-known functions for rounding numbers are: INT, ROUND, ROUNDUP, and ROUNDDOWN. Here's how to understand and use them.

Spreadsheet Techniques

How to Change Text to Columns When You Copy and Paste in Excel

I recently needed to copy a bunch of numeric tables from a pdf file into Excel. When I pasted the first table, all the data was pasted into column A, with a space between each number. Here's how I set up Excel to automatically change text to columns when I pasted.

Excel Documentation

Document Excel Worksheets with Pictures That Include Row and Column Headings

When you document your Excel worksheets, give your readers complete information by including row and column headings in the illustrations of your worksheets.

Excel VBA

Use Excel VBA to Test Report Calculation Times

An Excel expert wrote me that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said, than INDEX-MATCH. However, his testing method was flawed. Here's my correct VBA routine, which you can use to test your own report calculation times.

Spreadsheet Forms

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

In Word, you can set up forms that you can tab through, allowing you to enter your data easily. Here's how to do the same thing in Excel.

Excel Financial Functions

Find Future & Present Values from Scheduled Cash Flows

Here's how to calculate Future Values and Present Values from cash flows that aren't necessarily periodic.

Excel VBA

Declaring Variables in VBA: Three Keys for Success

You can reduce your errors in VBA, and simplify your programming considerably, if you dimension your variables correctly. Here's how.

Worksheet Techniques

How to delete blank or unneeded rows in your worksheet

When you bring data from another source into an Excel worksheet, the data often includes rows that you don't want. Often, these will be blank rows. At other times, you’ll want to delete rows with irrelevant data. Here's one way to do that easily.

Excel Error Handling

How to Improve Error Handling in Excel Spreadsheets

Many formulas are susceptible to errors. Here are some general techniques for setting up your formulas to deal with them.

Worksheet Techniques

How to End Jaggies in Large Excel Headlines

When you apply large font sizes to your report titles and headlines in Excel reports, you probably will notice that the fonts have "jaggies." Here's how to eliminate them.

Excel VBA

You Usually Don’t Need to Select an Object in Excel VBA

Most of the time, your VBA programs don't need to select worksheets or other objects to do something with them. Here's a better technique.

Excel Cube Functions

Excel Spreadsheet Functions that Read Values from Cubes

BI systems for Excel can give your spreadsheet formulas easy access to massive amounts of data. Here are spreadsheet functions that read data from OLAP cubes offered by three different vendors.

Excel Data Management

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

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.

Data-Management Strategies

How to Report Periodic Data from Excel Databases

Do you need to update your Excel reports with daily, weekly, or monthly data? Here's a low-maintenance way to do it.






Dashboard Reporting With Excel


IncSight DB Excel Dashboards


Charley's SwipeFile charts


IncSight QnE Excel Dashboards