Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel

Posted on May 12th, 2016 in Excel Howtos , hacks , Huis , Posts by Hui , Quick Tip - 16 comments

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:

 

 

 

Written by Hui...

Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel”

  1. Graham says:

    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

  2. Ronnie says:

    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.

  3. Pedro Paulo says:

    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

  4. Ian Wilson says:

    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.

  5. Vishal Onkar says:

    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.

  6. Christine says:

    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.

  7. Denys Calvin says:

    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).

    • Doug Glancy says:

      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!

  8. target says:

    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

    • Ronnie says:

      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

      • target says:

        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')

  9. prashant99 says:

    trace precedent Ctrl+[
    back to original cell F5+enter

  10. efand says:

    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

  11. Martin says:

    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!

  12. Manjunath says:

    Could you please help me getting trained in MS Excel and MS Access.

    Please

    Thank You

Leave a Reply