5 Keyboard shortcuts for writing better formulas

5 Keyboard shortcuts for writing better formulas:
As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.


So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas.

1. F2: Edit a formula cell. When you select a cell and press F2, Excel places cursor at the end and lets you edit the cell value / formula.
2. F3: Paste names in to formula. When you have a lot of names, often remembering them can be tricky. Whenever you want to type a name, press F3 instead. Excel will show a list of all names and pick what you want.
3. Tab: Auto-complete functions, names, structural references. As you start typing a formula, Excel shows auto-complete suggestions. Use arrow keys to select the function, name or structural reference you want, Press Tab key to let Excel type it for you. You can save a ton of time by just arrow key + tabbing.
4. F4: Switch reference styles – Absolute > Mixed > Relative > Absolute. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. You can use F4 key to do the switching. Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. (more: Guide to Excel cell references and when to use what?)
5. ALT + ESF: Paste Formulas only. If you just want to make a copy of the formulas and omit cell formatting etc., copy the cells with formulas, go elsewhere, press ALT + E and then S and F.  And you get a copy of the formulas alone.

Bonus Shortcuts & Tricks:

Writing and editing formulas is such an important part of Excel that there are many other useful shortcuts and tricks. Here are a few of my favorites:

• F9: Evaluate selected portion of a formula. Select a portion of a formula and press F9 to evaluate it alone. See the results and debug easily. (related: debug formulas using F9)
• CTRL + ` : Show formulas. Very useful when explaining your worksheet to others. Press CTRL + ` (back quote, usually the key above tab on left) to on / off show formulas mode. (related: auditing formulas & spreadsheets)
• Use mouse to edit formula ranges: When you select a cell with formula and edit it (by pressing F2), you can see these blue, green, red rectangles around the cells to which the formula is talking. You can move these rectangles or resize them to edit the formula input ranges. Very useful and very time saving. (more: Using mouse to save time in Excel)
• Fill down / side ways: Once you have a formula in a cell, you can drag it down or sideways (using mouse) to fill the formula down or across.

What are your favorite shortcuts for writing / editing formulas?

I am sure there are tons more shortcuts that I have omitted. So go ahead share your favorite ones in comments. Teach us something new. Go.
Also check out: Comprehensive list of Excel keyboard shortcuts.

Introduction to Structural References

Introduction to Structural References:
Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?
If so, you are not alone.
Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?
That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.
For example, you can write formulas like these:

• SUM(mySales[no. of customers]) to find how many customers we had.
• SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

Learn how to use Structural References and why they can change you life

Introduction to Structural References [video]
[Watch this on our YouTube channel]

Download Example File

Click here to download example workbook and play with Structural References to learn more.

Learn more about Tables & Structural Refs

• Introduction to Tables in Excel
• Using Tables with VLOOKUP formulas
• How to preserve table column references
• Customizing table formatting

Do you use tables & structural references ?

I use tables all the time. They help me stay focused on analysis & visualization instead of cell addresses.
What about you? Do you use tables & structural references? Please share your experiences, tips & ideas using comments.
Bonus Video: Here is a video I did with Bill Jelen (MrExcel) about this topic.
Personal update: We are at beautiful Woodbury Inn on Blue Ridge Pkwy and loving our stay. See this video.

How to get VLOOKUP + 1 value?

How to get VLOOKUP + 1 value?:
Here is a question someone asked me in a class recently.

I know how to use VLOOKUP to find a value based on search term. But I have a slight variation to it. I need to extract value below the cell VLOOKUP finds.

This is simpler than it sounds.
We can use INDEX + MATCH formulas to do this.
The syntax is like below:
=INDEX( value column, MATCH (search what, search column, 0) + 1 )
Why it works?
MATCH formula finds the position of what you are searching. By adding 1 to it and extracting the corresponding “values column”, we can get VLOOKUP + 1 value.
Homework for you
If you think finding VLOOKUP+1 is easy then I have a challenge for you.
Find the last match. Lets say in a table you have multiple items matching lookup value. How would you find the last item. Assume what you are finding is in A1, list is in C1:D20 and we want the value in 2nd column.
Go ahead and post your answers in comments section.

Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip?

Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip?:

Finally my Excel classes in USA are over. It was a lot of fun traveling to new cities, teaching Excel & dashboards to enthusiastic crowds and making new friends. As if that is not fun enough, we (Jo, kids & I) are going on a 2,000 mile, 2 week road trip starting today.
Although I am enjoying all this, I also feel bad for not taking enough time to share new tricks, ideas & techniques with you here. So, I have a wacky, wild & awesome plan for you. Join us on our road trip.
That is right. You can join me on our road trip and see what I see, learn some pretty cool Excel tricks, all while sipping coffee and stretching legs in the comfort of your office cubicle. No oppressive summer heat, no driving thru a long turn-pike looking for next rest area while your daughter is screaming in the back; ‘daddy, I need to go now.’ or no  drinking three coffees in a row so that you can access free wi-fi and check your email.
So buckle up. Err, I mean unbuckle and join us on this road trip.

What is going to happen?

Simple. While I am on road, each day I will be posting a new Excel tip or article. I will accompany it with a short snippet of what we are doing, include a photo or two of the beautiful things we are seeing.

Sounds Exciting, What should I do?

Nothing really. Just follow our journey for next 2 weeks. May be tell your colleagues or friends about it so that they too can enjoy. And if you happen to be in Raleigh or Pittsburgh or DC, ping me. We may be able to catch up.

So where are we going?

Well, I could tell you all about our Road trip using a map or several bullet points. Heck, I could even use a fancy image to tell you what we are up to. But you know that is not my style. So I have the right thing for you. So I present to you…,

Interactive Map of our Road Trip

That is right. First take a look at our road trip details:

How is this chart / map / interactive thingie made?

I wish I can sit here and type out all the little details about this. But I must rush now and pick up our rental car. So here is the recipe in a nutshell.

1. First I created a Google Map with all the locations we are visiting. (link)
2. Took a screenshot of the map. Embedded it in to Excel and cropped it.
3. Assuming the top left of the map is (0,0) and bottom right is (2.2, 3.2) figured out the x,y co-ordinates for all the markers by trial and error. (Hint: apply grid lines at 0.1 so that you can easily find marker positions).
4. Then created an agenda table with below structure.
   
   
5. Added a scrollbar form control and set it from 1 to 16. Linked it to a blank cell. (related: Introduction to form controls)
6. Using scrollbar selection, fetched corresponding row details from above table.
7. Created an XY scatter plot with (x1,y1) and (x2,y2) values for selected row.
8. Set cropped map image behind the chart’s plot area and resized things until they align ok.
9. Using text boxes, fetched various details for selected scrollbar value (date, heading, details, Excel tip).
10. Color and format so that everything looks good.

So there you go. Now, you know how to create your own map / chart / interactive thingie.

Download Road Trip Interactive chart

Click here to download the workbook and play with it. Examine the way chart is set up (ungroup it to move things) and various INDEX formulas to understand this better.

More on Maps & Excel

If you like to combine maps & data, then Excel is not going you help you much (unless you are using either Power View or Geo Flow). But there are a few ways to get maps in Excel. Check out below examples to learn more.

• Lithuania at a glance (Choropleth maps in Excel)
• Count-down timer on a map
• Visualizing Olympic medals by country
• Tracking Hurricane Sandy in Excel

So see you tomorrow, from Waynesboro (VA). Until then, stay in your lane and cruise.

We Want You – Revisited

We Want You – Revisited:

Chandoo.org Wants You

Over the past 5 years Chandoo has written about 1,500 posts on all things Excel and Hui has contributed another 80 posts mostly targeted at the application of Excel techniques to real life situations.
Two years ago we asked you “Was what we were posting actually what you wanted to see ?”
http://chandoo.org/wp/2011/06/22/we-want-your-ideas/
71 people responded with 101 ideas which were summarized here:
http://chandoo.org/wp/2011/07/22/we-want-your-ideas-results/
But that was two years ago
So again we’re opening the floor to you, with a single question:
What would you like to see discussed in future posts at Chandoo.org ?
Your ideas can be as specific or general as you like:
One Rule only: The Idea must involve the Functionality, Use or Application of Excel !
We cannot guarantee that your idea will result in a Post, But if you don’t ask, you won’t receive
We will do our best to schedule posts where most requested and suitable skills and time is available by authors.
So, What would you like to see discussed in future posts at Chandoo.org ?
Let us know what you’d like to see in future posts in the comments below:

A quick Excel tip while on bike…

A quick Excel tip while on bike…:
As you may know, I am in USA and having a lot of fun exploring new areas, meeting people and conducting live classes. While all of this is enjoyable, I am also feeling guilty because I am unable to squeeze time to share Excel tips on the blog.
So to make it up to you, I recorded this short video (2 mins) with an Excel tip while bicycling in beautiful Euclid Creek Reservation in Cleveland on Friday. Watch it below.


[Watch this video on our youtube channel]
Related: More keyboard shortcuts and a game to test your Excel keyboard skills.