>

# 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.

### Excel Pain Reduction

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

They'll also make your reports more accurate.

### Formula Power

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

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

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

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

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

Excel reports and analyses tend to use similar types of date calculations.

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

How 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

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

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

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.

### Worksheet Functions

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

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

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.

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

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.

## 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.

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

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.

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

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

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

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

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

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

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

## How to Create Two-Dimensional Lookups in Excel Formulas

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.

## 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.

## An Introduction to Excel's Normal Distribution Functions

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

## Read a Text File with VBA, and Write the Text 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.

## How to Create and Use Dynamic Range Names in Excel

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

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.