Excel Tips, Tricks, Cheats & Hacks – Readers Edition

Over the last month we have seen some 52, Excel Tips, Tricks, Cheats & Hacks presented by some of the best Excel practitioners on the net:

Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition

Excel Tips, Tricks, Cheats & Hacks – Chandoo.org Excel Ninja Edition

Excel Tips, Tricks, Cheats & Hacks – Notable Excel Sites Edition

Excel Tips, Tricks, Cheats & Hacks – Readers Prequil

 

In this final post I am presenting a compilation of Readers Contributions.

These have been compiled from comments on the above 4 posts and submissions sent directly to me.

I hope you enjoy the following Excel Tips, Tricks, Cheats & Hacks – Readers Edition

 

001. Toggle the Absolute/Relative $ Sign in Formulas using F4 – Desk Lamp

Instead of typing $AA$12 simply type AA12 then press F4.

Press F4 to Toggles through the sequence: AA12 -> $AA$12 -> AA$12 -> $AA12 -> AA12

You can read about Absolute vs Relative Cell References here

 

002. Current Region – Peter Carr

My favorite tip is the CurrentRegion of a range.

CurrentRegion is the contiguous range of cells starting from a cell, and moving out in all directions until an entire blank row or column is reached.

The current Region of the Yellow Cell above is the Red Outlined Area

Keyboard
From the keyboard you can do this by pressing Ctrl+* or Ctrl+A, which is a shortcut for GoTo, Special, Current Region.

VBA

In VBA you can use the Range.CurrentRegion property

If there is a block of data in B4:F10 with blank rows & columns around it

Dim myRange  as Range

myRange = Range(“C8”).CurrentRegion

will set myRange to $B$4:$F$10

To identify the number of rows in a contiguous region.
e.g. intNumberOfRows = Range(“FirstCell”).CurrentRegion.Rows.Count

003. Select the Current Region using the QAT – Christine

In addition to the techniques described by Peter above, you can select the Current Region by adding an Icon to the Quick Access Toolbar.

Click on any cell and then click on the icon or use Alt+4

 

004. Find the Alt-Shortcut Key Number for the QAT – Hui

In the “Select the Current Region using the QAT” post above, Christine showed us how to use the Current Region Icon  to quickly select the current Region. But how do we know it is the 4th Icon?

The Alt Number is Position dependent, in the example above the Current Region Icon is the 4th Icon from the Left in the QAT and so it is accessed by Alt+4

But by simply pressing the Alt key, Excel will show you the shortcut numbers for the QAT and all other Tabs

So we can see that the Select the Current Region icons is yes, No 4 and so Alt+4 is required to activate it

We can also see that the Record a Macro icon is number 08. To use that You use Alt+08 (Using the Number keys, not the numeric keypad)

Using Alt also shows you all the Tab shortcuts as well

 

005. Stay on the Current Cell after you press Enter – MF

Typically when entering data as you press the Enter key, Excel advances the current cell to the next cell as defined in the File, Options, Advanced, Editing Options menu

To stay on the current cell Simply press Ctrl+Enter instead of Enter

You can set your default move direction or disable Move Selection permanently by changing the option in the File, Options, Advanced, Editing Options menu:

 

006. Close a File Shortcut – Johnathan Cooper

Simply pressing Ctrl+W closes the current file

If the file has changed since the last save you are given the option to Save the file before it closes

 

007. Keyboard Shortcuts – Chirayu

Hide columns – CTRL + 0
Apply Filter (alternative) – SHIFT + CTRL + L
Clear Filter – ALT + D + F + S
Drag Down – CTRL + D
Drag Right – CTRL + R
Drag Up – ALT + E + I + U
Drag Left – ALT + E + I + L
Value Paste – ALT + E + S + V
Format Paste – ALT + E + S + T

You can find a comprehensive list of Keyboard Shortcuts at: Chandoo.org Keyboard Shortcuts

 

008. Use AutoCorrect to write formula – Wynn Hopkins

My favorite trick is using AutoCorrect to help write INDEX MATCH formulas..

Copy the following line into AutoCorrect and then use iii as the text to replace

=INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0)

This way whenever you need INDEX MATCH you just type iii and AutoCorrect kicks in and you are 3 double clicks away from a robust formula.

AutoCorrect is found in the File, Options, Proofing Menu

Contributor: Wyn Hopkins

 

009. Fill Blanks in a Data Table before use in a Pivot Table – RobD

When building pivot tables, it helps to have a full column of like values, so if you have a set up such as:

Where the data area has blank cell

Use this handy VBA

‘Change the MyCol value to match your value

Becomes…

Note: The code copies the text above the blank cell, and so the user must be careful that this is a valid assumption

 

010. Fill Blanks in a Data Table before use in a Pivot Table II – Jomili

Extending the technique shown above, Jomili supplied some VBA code that does the same as 007 above, except that it handles Multiple Columns at once as well as allowing Formulas to be converted to Values in the final result

So

becomes

by using this code:

011. QAT Copy/Paste Shortcut – Ian Watkins

By assigning the Copy, Paste Values and Paste Formulas Icons to positions 1, 2 & 3 of the Quick Access Toolbar

Instead of doing a big move of my hand from Ctrl+C to Alt+2, I can just move a finger from Alt+1 t copy

Click on the new cell and press Alt+2 or Alt+3 without moving my hand

Speeds things up quite a bit!

 

012. Customize Markers in a Chart – Chandeep

Customizing markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/

 

013. Charting Hacks to work faster – Chandeep

Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/

 

014. Seven Date formulas to make life easy – Chandeep

7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/

 

015. Customised scrollbar using VBA – Chandeep

Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/

 

016. Adding Direct Legends – Chandeep

Adding Direct Legends – http://www.goodly.co.in/how-to-add-direct-legends-to-the-chart/

 

017. Excel Ninja Menu – Krishna Khemraj

Select a cell or range then move till the 4-way cross appears.

Then Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.

When you click the fill box on a Date and Right Click and Drag it down, a lot of amazing Date options pop up.

 

018. Copy & Paste Filtered Data Only – Patricia

If you try to copy subtotaled data (and in earlier Excel versions filtered data), when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special.

Click Visible cells Only and click Ok.

Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.

 

019. Clear Filters for the Current Column – Graham

With a table that is filtered, ensure the active cell is in the header of a filtered column and hit ALT + Down Arrow + C to clear the filter for the current column

 

020. Names Formula Tips – Pedro Paulo

You can bring up the Name Manager in Excel by pressing Ctrl+F3.

This lists the names used in your current workbook, and you can also define new names, edit existing ones or delete names from the Name Manager.

You can define several named ranges using data that’s arranged in neat tables. Excel creates named ranges from your selection and uses your data headings as the new names.

Make sure your data has headings (top row, left column, bottom row or right column) as these will turn into the names of your named ranges
Select the data including headings, press Ctrl+Shift+F3, in the dialog box select where your headings are (top row, left column, bottom row or right column) and click Ok.

Field Names which include spaces will be replaced with underscores

eg: Account Code will become the Account_Code named formula

 

021. Avoid Division by Zero – Ian Wilson

If a formula returns a number value, the Iferror() function can be used to isolate a returned value of zero.

You just need to utilize reciprocals:
1/(1/x) = x, however, if x = 0, then the function is an error.
My most common use of this feature is to return a blank instead of a 0.
=iferror(1/(1/sum(range)),””)
This could also be used to avoid division by 0 or replace 0s with a string.

 

022. Text to Columns Shortcut – Vishal Onkar

When working with lots of Text or CSV Files you invariably end up using the Text to Columns function repeatedly

This can be accessed by the ALT+D+E  keyboard shortcut

023. Convert a Month in Words to a Month Number – Denys calvin

To convert a month in words (i.e., “August”) to its number (i.e., “8”), use, at least, the first three letters of the word in the following formula: =MONTH(“mmm”&1)

=Month(“March”&1) returns 3

=Month(“Mar”&1) returns 3

 

024. Trace Precedent/Dependents – Prashant99

Trace precedent cells Ctrl+[
Trace dependent cells F5+Enter or Ctrl+]

 

025. Resize Columns – Target

I routinely get sheets with data all smashed up which I hate and I’ve never been able to find a shortcut to do this.

To get around this I use the following VBA and assign a shortcut key (CTRL+Q)

It can be a nuisance if I’ve intentionally hidden columns, but the convenience far outweighs the inconvenience

 

026. Format Table Header Row – Ronnie

I use Tables many times a day and have a simple macro to give me a consistent Table format

The VBA Code:

027. Quickly Jump to Range – Efand

Type the range address directly in the Name Box and then press Enter to select it.

e.g: type A3:A6 will select its ranges without using any clicking and dragging

 

If you select a Range say B3:B6, then type a Name in the Name Box “From_Date“, Excel sets up a Named Formula referring to that range

If the Named Formula already exists, eg: From_Date, Typing From_Date into the box will take you to it.

You can also use the Drop Down next to the Name Box to select existing Named Ranges

 

028. Easily delete all Non-Formula cells – Martin

To easily delete all none-formula entries in a worksheet in one go:

Goto Home, Find & Select, Constants

This selects all cells that do not contain a formula.

Then just hit the delete button and you are done!

 

029. Reset all Cell Comments to the Same Style – Hui

To Reset all Cell Comments to the Same Style simply copy this code into a code module in your workbook

Edit the style parameters to suit your need

Run the code with F5

 

Closing

Many many thanks to the Microsoft Excel MVPs, Chandoo.org Ninja’s & My Favorite Excel Websites Authors for the 52 and You for the 29 Excel Tips, Tricks, Cheats & Hacks that have been showcased over these past 5 posts.

I hope you get to to revue all the tips and pass comments and appreciation back to the authors as appropriate.

I will re-run this series in May 2017 so keep a list of your new Excel Tips, Tricks or Hacks handy.

If you have any Excel Tips, Tricks or Hacks, Don’t be afraid to share them below in the comments: