For business users of Microsoft Excel Free guides and templates
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.

Formula Power

Advanced SUMIFS Calculations with Criteria Lists

IIn this final article of the SUMIFS series, you'll learn more reasons to use advanced multi-criteria lists in marketing and finance.

And you'll learn other options for using this powerful method.

Formula Power

How to Create Summarized Financial Statements with SUMIFS Criteria Lists

In one formula, you can summarize any number of specific accounts in a list, adding or subtracting them as you want.

This makes it easy to summarize financial data from an accounting trial balance.

Formula Power

How to Use SUMIFS with Criteria Lists, Summarizing Sales

The SUMIFS function returns a sum for nearly any number of single criteria.

But here's how you can return the sum for many criteria listed in a Criteria List.    

Rules of Thumb

Excel and the Rule of 72

The Rule of 72 is a guesstimate of how long it will take an investment at a specific interest rate to double in value.

But how accurate is this rule? This Excel analysis provides the answer.

Business Planning

How to Audit and Improve Excel Business Plans

Excel is a great tool for creating business plans. But you need to make them auditable and as accurate as possible.

One massive Excel business plan offers some great ideas about what NOT to do. 

Lookp Formulas

Tasks that VLOOKUP Can't Do

INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster.

In this final article of the series, six examples of what VLOOKUP can't do will build your lookup toolkit.

(Includes free downloads of lookup examples.)

Lookp Formulas

The MATCH Function

This is the second of a three-part series designed to INDEX-MATCH clearer.

The article explains all three types of the MATCH function, and why you'll use of these about 95% of the time.

(Includes free downloads of lookup examples.)

Lookp Formulas

The INDEX Function

INDEX-MATCH is Excel's most-powerful lookup method. But many users find it challenging.

In this first of a three-part series, you'll learn about the powerful INDEX function.

(Includes free downloads of lookup examples.)

Excel Alerts

Conditional Formatting with Formulas

Excel offers two types of conditional formatting: formattting a range based on the value of each cell, and formatting a range based on formulas. 

Here' how to use the second approach.

Time-Value-of-Money Formulas

Help with Excel's Most Confusing Time-Value-of-Money Functions

These two time-value-of-money functions have caused much confusion over the years...largely because their Excel help topics have omitted some key information.

Here's the information that will help you to use these two functions easily.

Excel Pain Reduction

Eight Tips to Make Excel Easier to Use

Here are eight simple tips that will make Excel easier to use,

They'll also make your reports more accurate. 

Formula Power

Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

In Excel Tables, you can filter on any two conditions in a column. But wouldn't it be nice to filter on any number of items in a separate list?

You can, if you use Excel's SUMPRODUCT function in a calculated column.

Formula Power

The SUMPRODUCT Function's Undocumented Method

SUMPRODUCT and SUMIFS are Excel's two most powerful functions for returning filtered data from a table. SUMPRODUCT is more powerful, but SUMIFS is faster.

Unfortunately, Microsoft's documentation about SUMPRODUCT lacks critical information: SUMPRODUCT actually has two syntax methods, not just the one method that Microsoft describes.

Financial Functions

How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel

SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell for many items. 

Financial Functions

Excel’s Five Annuity Functions

Most loans and many investments are annuities, which are payments made at fixed intervals over time.

Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Basic Excel

Save Time with Excel Range Selections

One of the big differences between early-stage Excel users and gurus is that gurus can do simple things in Excel much more quickly than other users can.

They can do this because they understand how Excel works in situations that most Excel users experience frequently. Here are some examples...

Using Date Formulas

Nineteen Date Calculations in Excel

Nineteen date calculations in ExcelExcel reports and analyses tend to use similar types of date calculations.

This article explains nineteen of them, using DATE, EDATE, EOMONTH, and other functions.

Advanced Lookup Formulas

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

Multi-Criteria Lookups in ExcelHow do you write a formula that returns the Manager's name from this Excel Table for a specified Date, Region, and Product?

Here are two ways to do it...

Excel Growth Rates

How to Calculate BOTH Types of Compound Growth Rates in Excel

If you search the web to learn how to calculate a growth rate in Excel, you’ll likely learn about only one type of rate. That’s too bad, because in business, we often need to calculate at least TWO types of growth rates.

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.

Reporting Strategies

Ten Reasons Excel-Friendly OLAP Belongs on Excel Users' Desktops

What's the most valuable technology for business users of Excel? It may well be the multi-cube database technology, called OLAP.

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.

Free Excel Dashboards

Charley's SwipeFile charts