What insights can you gain from the following chart of Apple’s quarterly revenues over the past 18 years?

I see two things here:

How to Use INDEX-MATCH, Part 2: The MATCH Function

by Charley Kyd on December 19, 2017

In part 1 of this series about INDEX-MATCH, I began the article with these two examples:

Formula 1:  =INDEX(Product,2,1)
Formula 2:  =$B$4 

I explained that in the specific workbook I was using, both formulas returned exactly the same result.

Well, soon after Excel was first introduced, decades ago, I was talking with the Excel Program Manager on the phone. With examples like those two formulas in mind, I told him that INDEX was a useless function because it requires that we enter numbers for the row and column arguments.

“If we know what row and column numbers to use,” I said, “we could just as well enter the cell address. So what’s the point of INDEX?”

(Click here to continue.)

How to Use INDEX-MATCH, Part 1: The INDEX Function

by Charley Kyd on December 19, 2017

Which is best, VLOOKUP or INDEX-MATCH?

You might be surpised at the question, because the answer never has been in doubt. INDEX-MATCH can do anything that VLOOKUP and HLOOKUP can do, and a lot more.

Here are five specific reasons that INDEX-MATCH is better than VLOOKUP:

  1. It’s never slower than VLOOKUP and can be much faster.
  2. It returns a reference rather than a value, which allows us to use it to do more things in our formulas.
  3. It doesn’t care where the result array is with regard to the lookup array…even in a different workbook.
  4. It can return approximate matches from data sorted largest to smallest.
  5. It can look up either vertical or horizontal data, with no need to change functions.

The major problem with INDEX-MATCH is that…

(Click here to continue.)

Conditional Formatting with Formulas

by Charley Kyd on December 19, 2017

Excel offers two types of conditional formatting. Although both types are very useful, one of them is more widely used than the other…probably because more Excel users understand it.

Excel Table without conditional formattingThe most-used method conditionally formats a range based on each cell’s value. This type not only can apply normal formats to cells, it also can display data bars and icons.

The other type conditionally formats a range based on formulas that can reference any cells. It’s the type I’m going to cover in this article.

(Click here to continue.)

The Basics of Professional Excel Charting

by Charley Kyd on December 19, 2017

When you’re reporting, you can’t run a cable from your data to your readers’ brains. But the next-best alternative is to use great charts.

By “great charts” I mean charts that are relatively small, easily understood, and have a professional appearance.

Unfortunately, few Excel charts have that professional appearance. To illustrate, here’s a standard Excel chart for some artificial data.

(Click here to continue.)

How to Find and Use Excel’s Best Hidden Features

by Charley Kyd on December 19, 2017

Excel has many outstanding hidden features. Here are a few that I use frequently…

  • In Excel 2013 and above, when you close Excel’s last open workbook you also close Excel. But you easily can keep Excel open even when all workbooks are closed.
  • You easily can nudge charts into position.
  • You easily can assign Windows-searchable keywords, categories, and other properties to your workbooks.
  • You can click and drag to select multiple charts and drawing objects on a worksheet.
  • You can close all workbooks with one command.
  • And you can do a whole lot more.

The key to finding and using these features is to experiment with Excel’s Quick Access Toolbar (QAT).

(Click here to continue.)

You can set up cascading list boxes so that your second list of items changes in response to the item you selected from your first list.

You could use a primary-secondary list structure like this to choose the names of people in the selected department, the products you buy from a selected vendor, and so on.

Continue here…

When each item in a primary list is associated with a different collection of items in a group of secondary lists, you can use cascading lists boxes to manage those secondary lists.

You could use a primary-secondary list structure like this in many ways. For example…

  • The primary list could be names of departments and the secondary lists could be the names of people who work in each department.
  • The primary list could be the names of competitors and the secondary lists could be the regions in which each competitor is active.
  • The primary list could be the names of vendors and the secondary list could be the products you buy from each vendor.
  • Etc.

(Click here to continue.)

Most Excel reports, forecasts, and analyses I’ve seen in my career could serve as excellent examples of what NOT to do in Excel.

These reports fail for at least one of three reasons: bad strategy, bad structure, or bad technique. In this article, I’ll cover best practices in the general design strategy that you should use to create your reports. In future articles, I’ll cover report structure and technique. And in all articles, for simplicity, I’ll use “report” as the general term for all reports, analyses, forecasts, models, and so on.

In my experience, few Excel users actually think about their basic Excel reporting strategies. This a shame, because bad strategy puts many users on the path to bad reports before they even launch Excel.

Here’s how to avoid the worst reporting strategies…

[…click to continue]

Introduction to Probabilistic Simulations in Excel

by Charley Kyd on March 22, 2016

(This is the first article in a series. The second article is, How to Create Monte Carlo Models and Forecasts Using Excel Data Tables.)

Back when I created models and forecasts for employers, I KNEW that my results were going to be wrong, and I had no practical way to express any degree of uncertainty in my results.

For example, if I were forecasting profits for a period, my spreadsheet would use one number for my forecast of sales in a period, another number for my forecast of operating expenses, another number for my cost-of-goods-sold percentage, and so on.

I knew that each predicted number in my forecast probably would land somewhere between a best-case and worst-case value…probably as determined by a normal distribution as shown in this Excel chart.

[…click to continue]