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.

Excel Risk Map

Excel Risk Map:
This is a guest post by Vijay, our in-house VBA Expert.
Hello Everyone,
We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.
So go ahead and grab a cup of coffee and read this article that would help you in creating a Risk Heat Map in excel (will use some double shot espresso in the form of VBA code) to help us to the target.

Before we begin

First of all we will understand what we are trying to create here by looking at the image below.

You would have seen a picture like this while managing project risks.
So today we will be learn how to create this in Excel to become more awesome in managing projects.
What is important here is how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us here.


There is a Setup sheet in the excel file where we can add the names of the projects that we will use on the data table, as well as the Probability and Impact have been defined as data tables. This helps us in using their contents as drop down options in the data table.

Adding Named Ranges

We need to use the Name Manager to create named ranges to be able to use the data table columns as drop down items, this is show below.

1. 1.Type this in a blank cell and then copy “=tblProject[Project]”.
2. 2.Bring up the Name Manager by pressing CTRL + F3, or by going to the Formula’s Tab and clicking on Name Manager.
3. 3.Click on New
4. 4.Type the name lstProject in the Name box
5. 5.Paste “=tblProject[Project]” in the Refers To box and the click on OK.

Repeat this process for “=tblProbability[Probability]” and “tblImpact[Impact]”
Now you can go the actual risk data table and select the Project columns first blank cell and put in Data Validation List here, choose List and put the Source as lstProject. Repeat this for Probability and Impact cells. You will only need to this one time for the first row, new rows when added to the table will automatically contain these settings.
After we have created the above data table, we need to add 3 columns to the right side where we will setup the calculation that will be used to update the risk map.
a) First Column is named as “ProbabilityScore” Since probability has been marked as “A, B, C or D”, we would need to convert this into a number. This is done by using the below formula.

=IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),4,3,2,1),”")
b) Second Column is named as “SearchString”

=IF([@Status]=”Open”,CONCATENATE(“x”,[@ProbabilityScore]^4+[@Impact]),”")
c) Third column is named as “DisplayName”

=CONCATENATE([@ID],” “,LEFT([@Project],20),IF(LEN([@Project])>20,”…”,”"))

Understanding the SearchString Table

When creating the SearchString we are raising the probability score to the power of 4, this is what I have chosen you may select any number that you need. Below is the resulting matrix of numbers that we obtain by doing this.

The last columns in only used for trimming the name of the project to 20 characters of there is a big name, else the actual name of the project is used to display in the Risk Map.

Understanding the Code

So now we are ready to look into the VBA code that helps us in creating the Risk Map.


Public Function showRiskMap(inputRange As Range, searchString As String, dataRange As Range, separator As String)

Dim cntr As Long

Dim tempArray() As Variant

Dim tempDataArray() As Variant

Dim tempString As String
tempArray = inputRange.Value

tempDataArray = dataRange.Value
For cntr = LBound(tempArray) To UBound(tempArray)

If tempArray(cntr, 1) = searchString Then

tempString = tempString & tempDataArray(cntr, 1) & separator

End If

Next

showRiskMap = tempString

End Function


We are sending 4 parameters to this function which are

1. inputRange – this is the SearchString columns data
2. SearchString – this is a manual enrty such as “x257”
3. dataRange – this is the Display Name column from where we will pick the name of the project to display
4. separator – this is CHAR(10) which is a line break in case we have multiple projects falling in the same category

We are making use of Array’s here to pass the data from the Table column into the array and then a simple For loop to parse them and show us the results.
I hope you will enjoy this article and this assist in managing your projects in a much efficient way.

Download Excel Risk Map File

Click here to download the file & use it to understand this technique.

Do you use Excel for creating Risk Maps?

Do you also user Excel for creating Risk Maps? If yes please put in the comment below how do you use the same and what has been your experience. Leave a comment.

More on VBA & Macros

If you are new to VBA, Excel macros, go thru these links to learn more.

• What is VBA & Macros? Introduction
• Excel VBA Example Macros
• VBA tutorial videos

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
Click here to learn more about VBA Classes & join us.

About Vijay

Vijay (many of you know him from VBA Classes), joined chandoo.org full-time this February. He will be writing more often on using VBA, data analysis on our blog. Also, Vijay will be helping us with consulting & training programs. You can email Vijay at sharma.vijay1 @ gmail.com. If you like this post, say thanks to Vijay.