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.

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.

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 INDEX-MATCH, and compares the two
"lookup" functions with INDEX-MATCH.
The winner should come as no surprise.

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

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.

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's four better-known 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.

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

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.