  >

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

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

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

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

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.

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

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.

Lookp Formulas

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.

Lookp Formulas

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.

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

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

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.

The Most Powerful and Flexible Ways to Summarize Excel Data for Reporting and Analysis 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.

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