Quick Wins: Excel Keyboard Shortcuts

I have finally compiled a list of excel shortcuts few I knew and few collected from various Excel websites.

To download the file Click here

In total, I was able to collect keyboard shortcuts for 98 excel activities. I'm sure these save quite a lot of time and effort. But, I'm not happy.

Quick Wins: Shortcut to type multiples of 10 in Excel

Wishing you all a very happy and prosperous new year 2011.

Starting this year, I’ll start sharing quick tips under the head “Quick Wins”. Quick wins that save time and effort and importantly easy to learn and execute.

How often do you need to type multiples of 10 in excel (10 0r 100 or 1000 or 1000000 etc)? If that is multiple times in a day then this tip is for you. I’m sure this will drool you (Unless, you are already aware of it!!!).

Typing just 10 or 100 is easy in excel. But what if you have to type 1,000,000? (A million, a billion or greater than that).

 How easy is it to type 1E6 instead of 1,000,000? 3 letters instead of 7! Explanation below

Awaiting New Year - 2011

I'm already stressed out this year. Already awaiting new year and started to count down. For all who are already exhausted like me, I created a file that counts down days, hours minutes and seconds to step into new year.

Just a preview of the file I created.

What are you waiting for? Go ahead click here and download the New Year Count Down file. I created this in Excel 2007. So, you can open thisin Excel 2007 and above. But if you are in Excel 2003 and you have a translator you can still use this.

Wishing you a very happy and prosperous new year in well advance.









Few techniques I used to create new year count down file are:

Go To Special series. Select visible cells only

Welcome to the nineth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are visible cells?

How often you filter the data in your spreadsheet? How often you hide some rows or columns from your data? I know your answer. Quite a number of times...Correct? If your answer is yes then read on and answer some more questions patiently.

How often you want to select and copy only the data you filtered and how often you want to select and copy only the rows / columns that are visible (excluding the hidden rows and columns)? This option is for you if you have failed copying this data and ended up pasting the whole set of data instead of only the filtered / visible data.

Go To Special series. Select Last Cell

Welcome to the eighth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

Select Last cell. Cell that has data and / or formatting. This is one of those most used excel option people often use.

Most often we need to go to the last cell in the worksheet we are working.

Go To Special Series. Select Precedents and Dependents

Welcome to the seventh post of this series – Select Precedents and Dependents. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are Precedents and Dependents?

As we all know that a result of a formula or a function is derived using a single or a set of variables and / or constants. In short, result of a formula is an outcome of one or more constants or variables. Precedents are the inputs used to get the result. Dependent is the result itself. What does it mean? The active cell must be a formula. Yeah, I know, that’s obvious. But I wanted to mention..Just in case…

Say for example, A = 1, B = 2 and C = A+B = 1+2 = 3.

In this example, A and B are the Precedents. The value of C is dependent on A and B. Hence C is dependent.

Isn’t it quite possible that A and B are also dependent on some other variables and or constants? Yes, you got that right! It is quite possible. That is why Excel gave us an option of selecting Precedents and Dependents that are Direct only and All Levels.

First things first

When you have to find the precedents / dependents you need to first select the cell to which you need to select precedents or dependents.

Example

Go To Special series. Select Row and Column Differences

Welcome to the sixth post of this series – Select row and column differences. Hope you are enjoying this series. Click here to read to navigate to go to special.

I’m sure this is one technique that will be useful for auditors, reviewers, quality checkers (all these are one job but different tags called in different companies. There might be some more which I’m not aware!). In the companies, I worked before there used to be one preparer and one reviewer (for a four-eye check or a quick sense check before the report is sent to the stakeholders. Reviewers job is to make sure the values are correct as per the inputs received and all formulas are correct and end result makes sense. This is one technique I adopted when I used to review the data.

Row differences:

Row differences select cells that are different to active cell from all the cells you selected in a row. By different, I mean cell values if they are constants or the formula differences if the cells contain formulae. When one selects a range of cells from a row one need to be careful in selecting the active cell. By default, excel selects the first cell as active cell. To change the active cell in the selection press Enter or Tab. Ensure you have the correct cell activated after which you wanted to find the differences in a row.

If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell.

Keyboard Shortcut: Press F5, then Alt+S+W

Below is a random data I picked to illustrate the same.

This is a gross profit calculation for four fruits sold in a market per week (I’ve shown data for two weeks). Gross profit is Sales minus COGS (Cost of goods sold). In this example, for week 1, gross profit for Apple should be C25-C26 (=50). Think as a reviewer! Check the formula in cell C27. It should be C25-C26 (Sales – COGS). If it is fine, select row 27 in this example from columns C: F. Press F5, then Alt+S+W. You will select the differences in row due to formula. The selected cells are wrongly calculated cells. (Now it’s your duty to mark your preparer an error! Well, if you do not want to screw him just let him know about the error. Am just kidding!!)

Check below the table and formulas for gross profit in table. If you rightly audit the formulae, you will observe gross profit for Grapes and Orange is not calculated using sales and COGS of Banana and Orange respectively (E27 shows D25 – D26 where it has to be E25 – E26, and F27 shows E25-E26 where it has to be F25 – F26). Same is the case with gross profit for week 2 for Banana (Cell D30). Formula was carried forwarded from apple gross profit calculation.

R-C diffs

	A	B	C	D	E	F
23
24			Apple	Banana	Grapes	Orange
25	Week 1	Sales	100	200	400	200
26		COGS	50	100	200	100
27		Gross Profit	50	100	100	200
28	Week 2	Sales	80	120	1000	120
29		COGS	40	70	600	70
30		Gross Profit	40	40	400	50
31

Excel 2007

Worksheet Formulas Cell Formula C27 =C25-C26 D27 =D25-D26 E27 =D25-D26 F27 =E25-E26 C30 =C28-C29 D30 =C28-C29 E30 =E28-E29 F30 =F28-F29

Copy the above data and formulas to your excel and play around.

Select range C27:F27 (Active cell is C27), Go to special and select row differences. You will see E27 and F27 are selected as they are different from the row (Formulas pointing to different column unlike the active cell in your selection). Below is a visual selecting the multiple rows and finding the differences.