Excel Tip: How to Easily Remember the Index-Match Formula

Lots of experts tout Index-Match as a better look up formula than venerable Vlookup.  However, it's a little hard to remember its formula (which is a mash up of the Index and Match formulas).

Here's how I do it:

=INDEX(Range with values I don't have, (MATCH(Look-up value, Range with values I do have, “0″))

Everything I have goes in MATCH; what I don't have goes in INDEX.  For example:
  • Range with values I don't have = Range of product IDs
  • Look-up value = Product Name
  • Range with values I do have = Range of product names
The 0 at the end of the formula means you're looking for an exact match.  Check out the Index-Match article linked above for a fuller explanation.

How to Bold a Cell in Excel Based on Another Cell's Contents

Ever want to bold a cell in Excel based on the contents of another cell?  Here's how...

Say you need cells in column P to be bolded if column A contains the word "total."  To do this you'd:
  1. First, select the cells that you want to which you want to apply conditional formatting.
  2. Then click Conditional Formatting from the top ribbon.
  3. Next select New Rule.
  4. Then click "Use a formula to determine which cells to format."
  5. Enter this formula in "Format values where this formula is true": =ISNUMBER(SEARCH("total",A1))=TRUE 
  6. Select your format; in this example, you'd use bold.
After this all the cells that have the word "total" in them cells against them will be bolded.

If you want to learn more about conditional formatting in Excel based on values in other cells, check out this excellent in-depth article at AbleBits.  If you want to add to that knowledge on how to format a cell conditionally based on its own text, check out this Microsoft Office article.

Excel Tip: Abbreviate Large Chart Axis Numbers

Charts are important way of summarizing data in Excel.  For them to work best, they need to be uncluttered.  One way to clean up a chart is to replace large numbers with an abbreviated version.  For example, you could replace 1,600,000 with 1.6M.

To do that, right-click on the chart's axis and select Format Axis.  Then click on Number in the dialog's left column and add this code in the Format Axis's Custom section:


If you're curious how Custom Formats work in Excel, check out these these great articles on the topic:

Excel Tip: How to Not Show Zero Values in Percentages

Often not showing zero values in Excel makes data easier to read.  Percentages by default display zeroes; if you want to replace that with a dash, blank, etc. use the following formula in the Custom Format section:

0.00%;-0.00%;"-"  (you can replace the "-" with blanks (" ") or anything else)

The easiest way to bring up the dialog is by hitting CTRL+1, which will get you what you see here:

If you're curious how Custom Formats work in Excel, check out these these great articles on the topic:

Show a Count of Open Tabs in Firefox

Ever open so many tabs in Firefox that you lose track of how many are running?  Then install TabCounter for Firefox. It's free and requires no restart to install.  It places a discreet counter just under your Minimize, Maximize, and Close buttons in the upper right of the browser window.