Excel Tips Submitted by You [Part 2]

It is Your Week @ PHD. You get to see all the excel tips shared by PHD readers like you. I am very excited about this. Today I rushed home from work and opened the google docs to see the tips submitted by you, and found several new and fun things. You can be part of this excitement too: just click here to share your tips with us.

Here is the part 1 of the series.

The first and foremost chart construction tip by Jon Peltier

Before you take any steps towards constructing a chart, get the data right. This will save time and effort, reduce frustration, and amaze your boss, clients, and that cute young thing in accounting. Well, your boss and clients anyway.

Consider it as a set of data extracted from a database: each row is a record, each column is a field, the first row is the field names.

First row of the data range: series names as column headers.
First column of the data range: category labels or X values.
Intersection of first row and first column: overcome your natural tendency for order and uniformity, and leave this cell blank.
Data is Series in columns.

A Flurry of Keyboard Shortcuts and Productivity Hacks by Barbara

I use several shortcuts

• F12 for ‘Save As’
• F2 for ‘Edit’
• F4 to switch between absolute and relative formula references, whilst in edit mode.
• Control & 1 (using the number keys not the number pad) for ‘format cells’

If you want to copy part of a spreadsheet to a powerpoint:
Highlight the area, hold shift, click edit in the menu, copy picture, choose ‘as shown on screen’ or ‘as shown when printed’.  Paste into the powerpoint slide.

If you put the insert rows, insert columns & group icons in you toolbar you can use them for the opposite function as well. Press & hold the shift key, then click on one of the tools, it will do the opposite.  This not only saves space in your toolbar, but I find it useful as I can not easily tell the difference between the insert & delete or group & ungroup icons.

Several ways to search text using formulas by Vishy

Download these examples and play with them.
Lookup into Substring

Let’s say you want to find Age in Column D by referring to Part of First Name in cell G3 as below
First Names are in B3:B7
Ages are in D3:D7
Part of First Name Reference in G3
{ =INDEX($D$3:$D$7,MATCH(TRUE,FIND(G3,$B$3:$B$7)>0,0)) } Must be entered as an array (Ctrl + Shift + Enter)

Use SEARCH instead of FIND for case-insensitive lookup
You can also use regular expressions – Download the examples
* refers to any number of characters, including null (i.e. no character)
? refers to exactly one character, not null

Multiple Criteria Lookup
Let’s say you want to find Age in Column D by referring to First Name in cell G4 and Last Name in H4 as below
First Names are in B3:B7
Last Names are in C3:C7
Ages are in D3:D7
First Name Reference in G4
Last Name Reference in H4
{ =INDEX($D$3:$D$7,MATCH(1,($B$3:$B$7=G4)*($C$3:$C$7=H4),0)) } Must be entered as an array (Ctrl + Shift + Enter)

Some Resources to help you understand these tips:

Introduction to Excel Array Formulas

INDEX Excel Formula – Learn by Example (Used to construct a dynamic chart)

MATCH Excel Formula – Tutorial & Examples

Absolute and Relative Formula References – A Beginner Guide

Jon, Barbara and Vishy, You are truly amazing human beings. We at PHD love you for these wonderful tips.