Excel Keyboard Shortcuts - Handy during day to day work

I've had few posts earlier on few excel shortcuts.

• Navigation powered by "Ctrl" in Excel
• Shortcut to insert current date in a cell
• Shortcut to AutoFilter

So, here I am, back with some more interesting shortcuts which will make your excel spreadsheet experience simpler.

Let me start with some "CTRL" combinations which will be very handy for formating cells to match your requirements:

• CTRL + 1 : Opens the "Format Cells" dialog box
• CTRL + 2 : is to make the contents of the cell BOLD (this is a toggle key, to unbold use same CTRL + 2)
• CTRL + 3 : is to make the contents of the cell ITALICS (this is a toggle key)
• CTRL + 4 : is to underline the contents of the cell (this is a toggle key)
• CTRL + 5 : is to strike through the contents of the cell (this is a toggle key)
• CTRL + 9 : is to hide the entire row (to unhide use SHIFT + CTRL + 9)
• CTRL + 0 : is the hide the entire column (to unhide use SHIFT + CTRL + 0)
• CTRL + ~ : is to show or hide formula (this is a toggle key)
• CTRL + - : brings up the delete cells dialog box
• CTRL + + : brings up the insert cells dialog box

Another set of shortcuts is with the combination of SHIFT and CTRL:

• SHIFT + CTRL + 1 : converts the number in the cell to decimal format with 2 decimal places
• SHIFT + CTRL + 2 : converts the number in the cell to time format (your default system format)
• SHIFT + CTRL + 3 : converts the number in the cell to date format (your default system format)
• SHIFT + CTRL + 4 : converts the number in the cell to currency format (your default system format)
• SHIFT + CTRL + 5 : converts the number in the cell to percentage format
• SHIFT + CTRL + 6 : converts the number in the cell to scientific format (your default system format)
• SHIFT + CTRL + 7 : adds outline border to the selected cell(s)
• SHIFT + CTRL + - : Removes the border from the select cell(s)

Get used to these common shortcuts till I manage to compile few more.

You can find more such posts on the internet. So make sure you refer and make best use of the shortcuts.

Alternative for SUMIF and SUMIFS

Ever wondered while using "SUMIF" function, the sum range cannot be more than one column!! This indeed is a big drawback while using a large data set where you need to sum matching certain conditions.

I often end up using multiple sumif with "+" which makes the formula very long. So was thinking of some alternative that can make life easier.

Thanks to the search engines these days, I did come across a workaround.

Refer to the above image:

In the above example, I have a table with 4 columns where H1 and H2 are identifiers. H3 and H4 is the data which needs to be summed up.

Condition 1 (alternative for SUMIF):
I need to sum values of H3 and H4 where the identifier H2 is "Y".
The formula will be =SUM(IF(B2:B5=B9,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this: 
{=SUM(IF(B2:B5=B9,C2:D5))}

Condition 2 (alternative for SUMIFS):
I need to sum values of H3 and H4 where the identifier H1 is "N" and H2 is "Z".
The formula will be =SUM(IF(A2:A5&B2:B5=B10,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this: 
{=SUM(IF(A2:A5&B2:B5=B10,C2:D5))}

Try this out in a larger data set and check your results.


Disclaimer: This will work only in a array i.e. without "Ctrl + Shift + Enter" , the result may be "#N/A"

Solving issues with "VLOOKUP"

VLOOKUP syntax

We often encounter "#N/A" while using VLOOKUP function to compare two lists. One of the most common problems that give rise to this error is extra spaces either to the end or to the beginning of the string.

What's the way out?

The simplest way out is to use "TRIM" function as a sub function.

Example

While trying to use VLOOKUP in the normal course, the result will be "#N/A", whereas with TRIM nested in the VLOOKUP function will give the positive result.

(TRIM function removes the unwanted spaces in the beginning and end of a string.)

Identify duplicate values in a list using "Countif"

Though MS Excel today allows to easily eliminate duplicate values in a list, have you ever tried to identify the number of entries that gets repeated in an excel list?

In the above example, we can identify the repeat entries using "COUNTIF" function.

Option 1

formula: =COUNTIF($D$1:D1,D1)
Explanation: This formula counts the occurrence of the value in the list i.e. on the first occurrence, the result will be one and on the second it will be 2 and like wise. (see the figure below)

Option 2

formula: =COUNTIF($D$1:$D$8,D1)

Explanation: This formula counts the total number of repetitions. If an entry appears thrice in a list, then the result will be 3 in all the occurrences of that entry. (see the figure below)

So start using whichever is convenient to you... :)

Power of Data Validation - 2

Have you ever wondered if we can have a dynamic drop down list i.e. a drop down using data validation that gets updated automatically with any change in the source of the drop down...

I often faced problems having a dynamic drop down list but ultimately I was able to find a solution to my problem.

Step 1:

Format the source list as a table and name the table (here I've named the table as "Fruits")

Step 2:

Go to Formulas -> Name Manager and click on New

Define any name in the dialog box

In the example, I've named it as "Names". Then click on Refers to field and select the table in the excel sheet. The refer to will appear as =TableName[ColumnName]

Step 3:

Now create the drop down list using data validation and mention the source as the name you defined. In this case "Names"

Here you go. The drop down list hence created is dynamic. Any addition or deletion in the source table of the drop down options, the validated cell gets updated automatically...!!

Excel is great!

Related posts:

http://blog.arvindkumar.com/2012/10/power-of-data-validation-1.html

http://blog.arvindkumar.com/2010/05/custom-validation-in-excel.html

http://blog.arvindkumar.com/2012/03/data-validation-in-excel.html