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

Related

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.

Related

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.

Related

Financial Functions
How to Perform Multiple Table Searches Using the SEARCH
& SUMPRODUCT Functions in Excel
SUMPRODUCT is one of Excel's mostpowerful worksheet functions.
Here, for example, you can use it in one formula to search text
in one cell for many items.

Related

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.

Related

Basic Excel
Save Time with Excel
Range Selections
One of the big differences between earlystage 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...

Related

Using Date Formulas
Nineteen Date Calculations in Excel
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.

Related

Advanced Lookup Formulas
Two Ways to Set Up MultiCriteria Lookup Formulas in Excel
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...

Related

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.

Related

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.

Related

Excel Tables
Read and Update Excel Tables Using SUMIFS and INDEXMATCH
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.

Related

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.

Related

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.

Related

Summary Reporting
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
You can return any number of items from an Excel database. Just use Data Validation to return one item and INDEXMATCH to return the others.

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

Excel Lookup Functions
Excel’s VLOOKUP function
seems to be more popular than the
INDEXMATCH function. This is too bad,
because INDEXMATCH offers more power and
flexibility.

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.

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.

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.

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!

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


Twodimensional lookups are easy
in Excel.

You can calculate frequency distributions with Excel's SUMIF, FREQUENCY,
SUMPRODUCT, INDEXFREQUENCY, and COUNTIFS functions.

Excel Shortcuts
You'll probably use these littleknown 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
Excel provides several statistics functions for working with normal distributions.
Here's an introduction to these functions

Excel VBA
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
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.

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.

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
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
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
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
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
When you use the INDEX function, here's how to test that it's giving you the data you expect.
Excel Summary Formulas
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
You easily can return any number of items from an Excel database. Just use Excel Data Validation to return the
item you choose, and INDEXMATCH to return the other items.
Excel Databases
You don't need to write a macro to delete rows or columns with specific data from a database. Use AutoFilter instead.
Excel Databases
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
Excel's Web Query tool provides a quick and easy way to import web data to Excel.
Excel Web Data
Excel offers two primary lookup methods: VLOOKUP and INDEXMATCH.
Here's an introduction to VLOOKUP and an illustration of
one of the few times you should use an approximate
match.
Excel Names & References
A visitor is confused about when to use "$" in cell references. Here's what every Excel user should know.
Range Names & References
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 DateTimes
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
Learn four VBA documentation standards that make your VBA code easier for you and others to understand.
Excel Text Functions
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
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
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
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 ErrorReduction
Simple spreadsheet edits can introduce costly errors. Here are three techniques that can help you to reduce such problems.
Excel Math Functions
Excel's four betterknown functions for rounding numbers are: INT, ROUND, ROUNDUP, and ROUNDDOWN. Here's how to understand and use them.
Spreadsheet Techniques
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
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
An Excel expert wrote me that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said, than INDEXMATCH. 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
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
Here's how to calculate Future Values and Present Values from cash flows that aren't necessarily periodic.
Excel VBA
You can reduce your errors in VBA, and simplify your programming considerably, if you dimension your variables correctly. Here's how.
Worksheet Techniques
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
Many formulas are susceptible to errors. Here are some general techniques for setting up your formulas to deal with them.
Worksheet Techniques
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
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
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
You can return any number of items from an Excel database. Just use Data Validation to return one item and INDEXMATCH to return the others.
DataManagement Strategies
Do you need to update your Excel reports with daily, weekly, or monthly data? Here's a lowmaintenance way to do it.







ExcelUser, Inc. Copyright © 2004  2016 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by
US and International Copyright Laws, and may not be copied,
reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission.
Terms of Use 
Privacy Policy 
Disclaimer 
Earnings Policy.

Ref:
900 000

