Over the past 3 weeks we have been able to showcase some Excellent Excel Tips, Tricks, Cheats and Hacks from some of the best excel practitioners on the planet.
These Excel Tips, Tricks, Cheats and Hacks were published in the following posts:
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
But now it is your turn !
Next week we will be highlighting what is Your favorite Excel Tips, Tricks, Cheats and Hacks.
These will be compiled and published with worked examples this time next week together with your name and any other details you want included.
You can either:
- Leave a comment below with your tip and details;
- Start a Conversation with me in the Chandoo.org Forums, or
- Email me directly to: ihuitson at gmail dot com.
All Excel Tips, Tricks, Cheats and Hacks posted in the comments of the above 3 posts will automatically be included:
16 Responses to “Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel”
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
Custom Ribbons
I create a custom ribbon and move it first in the list (so it opens when Excel starts). Then I add all the main commands I perform in Excel to that single ribbon. The first four commands are New, Open, Save, and Save As so I never (rarely) have to go to the File ribbon. Group commands with four or less to maintain the large icons. With this custom ribbon, I rarely have to go to any of the other ribbons. That saves me lots of clicking to find the commands that I need.
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 also 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
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.
Number crunching is my job and excel my first love. I work with a lot .CSV files and ALT + D + E...comes handy almost Everytime in converting that disastrous looking data into columns.
To select a region - place this icon "Select Current Region" Black rectangle with arrows on the corners, on the quick access tool bar. Place the curser in one of the cells in the region and click the icon.
To convert a month in words (i.e., "August") to its number (i.e., "8"), use the first three letters of the word in the following formula: MONTH("mmm"&1).
Denys, I love that trick and was very excited when I first saw it a couple of years back. The problem is I only need it a couple of times a year and it takes me a lot of experimenting to remember exactly how it works!
one of the things that bugs me is column sizing - 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)
Sub column_size()
ActiveSheet.Columns.AutoFit
End Sub
it can be a nuisance if I've intentionally hidden columns, but the convenience far outweighs that for me
Target,
I combine the AutoFit with a few other commands to isolate my headings. I use this several times a day.
Sub Header_Format()
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = 15
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
End Sub
works where your headers/splits are consistent, but mine vary constantly so I just use the hotkey for splits (CTRL+F-F).
I have a another macro for page setups (set margins to minimum, print 1 page wide, add a footer), but if you add a previously formatted workbook named 'book.xlsx' to your startup directory, every new book will match that formatting (and likewise for sheets, just save another book named 'sheet.xlsx')
trace precedent Ctrl+[
back to original cell F5+enter
Type the ranges directly in the Name Box and then press Enter to select it.
e.g: type A1:B12 will select its ranges without using any clicking and dragging
Hi,
here is one of my favorites. To easily delete all none-formula entries in a worksheet in one go:
Ribbon: Home - Find & Select - Constants
This selects all cells that do not contain a formula. Then just hit the delete button and you are done!
Could you please help me getting trained in MS Excel and MS Access.
Please
Thank You
@Manjunath
Chandoo.org only does work in excel at present
Visit: http://chandoo.org/wp/welcome/
Then scroll down and review the points 1, 2 & 3
They have many links and other information.