Share [Social9_Share]

What are your most productive excel tricks ?

Productive and Time saving Excel Tricks 1

Hello there, all you lovely and goodly people! Read this short story

The other day I was sitting with a friend helping him with some Recruitment Analysis Dashboard

Friend : Hey can we also see the demographics by position applied for ?

Me : (doing while I was saying it) So let’s add position in the Inputs Sheet and covert this into a Table, now let’s refresh our Pivot and add a slicer on Positions Field and apply our sexy formatting on it and stack it in our Dashboard!

Done! Dude check this now..

Friend : (with his jaw dropping) Man, you are fast. I love the speed

Sorry, I dint mean to brag at all :D. If you have stayed with me for a while you know this is easy, no brainer stuff.

Today let’s exchange some speed tricks and make each other a hell lot faster and to do that I need your help. Here is the plan!

 

I want YOU to write for Goodly!

Over the last few years I have written a ton of articles and the more unique problems I solve (or struggle with) I realize that I can’t ever write all the awesostuff on my own.

I can just write what I know, so I need you

 

Share your favourite time saving (productivity) hacks

  1. No matter what kind of work you do on Excel I am sure you must have developed a pattern of getting the work done faster.
  2. In the comments section please write your favourite Excel Shortcuts, tricks, hacks that help you go back home early 🙂
  3. The DOs
    • Put down your ideas clearly in the comments
    • Even links to other websites (in case the trick is longer) is absolutely fine
    • Share as many tricks as you want. Length no bar!
  4. The DONT’s
    • Don’t copy and paste a list of shortcuts (especially if you don’t use them)
    • Don’t think that your trick is too menial or unworthy. If it helps you get your work done, please please please put it down in the comments. It could save someone’s life 😀

 

Here is what I’ll do..

My target is to curate a 100 speed tricks and create a pdf and roll it out to you guys by the 20th of this month. And even before you ask.. Yes I will give you the credit if you contribute here

If you are too enthusiastic about Excel and have a ton of stuff to share. Please write an email to me at goodly.wordpress@gmail.com

 

The comments area is waiting for you 😉

 

  • I’ll do the honors first 😀
    If you are writing a formula (let’s say VLOOKUP) in a cell. While selecting a large range you may lose the sight of the cell where you were originally writing the formula and then you use the scrollbars to come right back up.
    Don’t do that – Instead press the shortcut CTRL + Backspace to come right back up to the cell where you were working
    This has saved me a ton of hassle while of switching between mouse an keyboard

    https://uploads.disquscdn.com/images/e3a73048c23caba7068b1ad744db2c403bd901cafdbd6a6637e574fd11822386.png

    • Karthik

      Cool one!!

  • Chiranjeev Chhabra

    Firstly Index match my favourite ( thanks to goodly I have learnt this trick here ) secondly I deal with large data sets where filter is required I use control+shift+L
    And I want same formatting very week on one of my sheet for that I have recorded a macro this all makes my work pretty fast
    The same work was done by previous staff he usually took a days time to do this 😉

  • Sonika Singh

    One of my most used trick is ctrl+shift+L (for filter), Alt+T+U+F (for evaluating formulas), Ctrl+1 (for Format Cells) last but not the least Vlookup and Match function. I am in love with these tricks as really make my excel run sprints. 😀

  • GraH

    My latest favorite is PowerQuery (GET & TRANSFORM) to import external data – multiple files even – like hasslefree and faster then many vba macros.
    So Chandeep, you still write VLOOKUP? Try RELATEDTABLE([FIELDNAME]), or the PowerPivot way. Joke aside, I was in awe when I learnt the POWER VLOOKUP from you. Man that is fast!
    CTRL + PAGE DOWN/UP are amongst my favorite shortcuts to navigate between worksheets.
    CTRL and or SHIFT in combination with the left mouse click to move (shift) or copy (CTRL) rows/columns of data with ease. Try holding down CTRL + SHIFT: indeed inserts a new copy of data.
    These were my top of mind tips.

  • Taney Khanna

    One Trick that fascinated me the most is the Picture Vlookup. Though I can rarely use it, but i dont miss a chance to use it wherever i can.

    • Zaur

      Hi, can you share the code?

  • Kim Hook

    I recorded a macro for “Centre across selection” and added it to my Quick Access Bar, now I can use it speedily and don’t resort to the dreaded “Merge & Centre” option.

    • Zaur

      Hi, can you share the code?

  • Jackie O’Neil

    ► My most used shortcuts for Microsoft Office are CTRL + c (copy), CTRL + v (paste) and CTRL + z (undo). I use them all day long at work.
    ► CTRL + b (bold), CTRL + i (italic), CTRL + u (underline) – easy to remember. I don’t use them all the time but it is so much easier than going clear up to the ribbon.
    ►I also love VLOOKUP. I have a workbook that has a hidden list of zip codes that populates the sheet I work on. I put in a zip code and the VLOOKUP populates the county. Big timesaver for what I do.
    ► Customizing the QAT (quick access toolbar) saves a lot of time since I have to print a lot throughout the day. It is a lot easier to click on “print preview” than going to file, print and then seeing the preview.
    ► Using the “page break preview” icon on the bottom right to adjust what gets on a sheet (doesn’t work all the time) or likewise, using the Page Layout tab and clicking on “print area” to set what I want to print on a sheet.
    ► Slicers. I have to stop putting them on everything, but they are easy to put on, use and besides they are cool looking. I love using these with pivot tables.
    ► “Remove duplicates” in the Data tab has saved me a ton of time with one particular report I do. It is so easy to use. On that same report, I have an array formula that sorts the data into age groups. After I have removed the duplicates, I paste it into a table with the array formula table on the side and bingo – numbers in each age group. Major timesaver.

  • Reshma

    My favorite shortcuts are – Ctrl+Shift+L (for Filtering data), Ctrl+- (to delete column), Shift+Space bar (select row), Ctrl+Shift + down/Up/Right/Left arrow (Selection data) , Ctrl +D (for fill up cell data down), Most favorite is Alt+= (for Auto Sum)…
    Thanks to goodly because of excel tricks I can able to do my work more faster and easy than earlier.

  • William

    My key ones are (in order of priority):
    1. CTRL+1 to format cells, shapes and charts
    2. QAT for the commands I use most
    3. CTRL+SHIFT+Arrows to highlight a range of cells
    4. Power Query to combine and reorganize data coming from multiple sources
    5. ALT+E+S to paste special

  • Ask Planner

    I myself use excel for Reporting/Dashboard purpose most of the time. So, I use the sorting function very often. I believe most of you might knew this keyboard shortcut, but it is very useful and save a lot of time for me.
    To custom sort the data
    1. Select the entire data range, which you want to sort
    2. Press Alt+D+S, you will get a customs sorting pop up.
    3. Now add levels to sort the data
    4. Publish the report.

  • Olaf Stempels

    https://uploads.disquscdn.com/images/e807ef97955dd5baabc36049a43b2df37d54743598d7d68fd064059d6cfd6d8a.png

    I made a simpel and easy to use countdown timer for projects for everybody to know D-day….file can be shared, for now only a screenshot

    • mma173

      I like this idea. I will incorporate it in all my feedback requests to remind the team of the deadline. But I will use a macro-free solution. Thanks’

  • Hi, My previous boss always used to tell me cut this row and put it in the top , and cut these 2 rows and put them down….a lot of mouse clicks here and there, so I googled and got the shortcut for this….let’s say you want to cut row 3 and put it in row 1. Highlight row 3 by using Ctrl + Shift + right arrow key from the starting point, use Ctrl + X to cut, go to row 1, place your cursor there and use
    Alt + I + E…Voila!..Done!…

  • Pavan Kumar Sistla

    Hello Chandeep,

    Below are the SooperShortcuts which I use and I am sure are used by many people. I like them to be called as Smartcuts.

    Shift + F11 –> To insert a new worksheet
    Alt O H R –> To rename a worksheet
    Alt O H H –> To hide a worksheet
    Alt O H U –> To unhide a worksheet
    Alt E M –> To move or copy a worksheet
    Alt E L –> To delete a worksheet (can’t be undone using Ctrl + Z)
    Ctrl + _ (Underscore) –> To remove borders for any selection
    Alt H B T –> To apply thick box borders around a selection
    Alt H B A –> To apply single line border for selection
    Alt O C W –> To adjust column width
    Alt O R E –> To adjust row height
    Alt H L –> To apply conditional formatting
    Ctrl E –> To apply flash fill
    Shift + F2 –> To insert a comment in a cell
    Shift + F10 –> To right click and get the menu in a particular cell

    Thank you for the knowledge sharing and opportunity!

    Regards,
    Pavan.

  • Ravi

    Hi Chandeep,

    1. While preparing a periodical reporting pack, I use Edit links in the Data tab regularly. This will help consolidation of information pieces by changing the links from previous files to new files with same sheet names. (Eg. While preparing May presentation file with various data like Market share, Revenue, Profitability, Sales volumes. All these files are independent. However, every month I consolidate these information into a reporting pack with the help of change source. where I just select May files by changing the source. It will populate the May data automatically.

    2. While copying the data from excel files to PPT, I use paste link option. It means whenever, there is a change in excel data, The presentation will update automatically just by updating links. This will help presentations with too many slides like more than 20.

    3. Ctrl+F1 to maximize the screen while presenting in Excel.

    4. Sumproduct is the one of the unique formula to tame big data and retrieve what is required. This will help populate the data required for charting/graphing. Interestingly, the attributes for your charting can be dynamic because Sumproduct can cover Vlookup, Hlookup, SumIF, SumIFS,Index, Match and combination of any of these.

    • GraH

      Hi Ravi,
      I’ve used the same tricks (1&2) for many years. People asking themselves: “How come you are so fast?”
      Just yesterday I demonstrated trick n°2 in a training. Priceless reactions.
      So I thank you for sharing them here. I find this kind of advice more valuable then knowing about the next shortcut you won’t remember.
      Knowing the shortcut saves you a few seconds at the most. Update a large PPT in automated way can save you up to a couple of hours every time you do it.
      Like I’ve said in my previous reaction, for using external data sources I definetely advice to switch to PowerQuery (GET & TRANSFORM in later versions).

  • Dev

    Close ALL Workbooks shortcut
    Hold the SHIFT key and click on close button

    If all of the open workbooks are saved then they will all be
    closed
    If any of the workbooks have not been saved then you will be
    prompted to save the file. The pop-up window will appear and you have to choose
    Save or Don’t save for each unsaved workbook.

    Below is the screen print for reference…

  • Dev

    Close ALL WorkbookS shortcut

    Hold the SHIFT key and click on close button

    If all of the open workbooks are saved then they will all be closed

    If any of the workbooks have not been saved then you will be prompted to save the file. The pop-up window will appear and you have to choose Save or Don’t save for
    each unsaved workbook.
    Below is the screen print for reference:
    https://uploads.disquscdn.com/images/7607caf9eca66dd5807775dfde99a242d43df400d663c9cefcd555b3b0bfc4e9.jpg

  • GraH

    To my surprise still a lot of Excel users don’t know about F4 to toggle relative/absolute references. When I ask what they do instead,… The answer is typing the Dollar sign manually. Are you kidding me?!! Or should I say: “Killing me, not so softly?”

    What about charting then?
    – Use Sparklines when showing the general trend is all you need (added a picture with an example)
    – One click heatmaps with conditional formatting Color Scales
    – One click bar charts with conditional formatting Data Bars (and sort your data)
    – ALT + F1 -> standard chart
    – In a series of charts: format one , then CTRL + C to copy a chart and CTRL + ALT + V to paste special then select “format” to apply the same format to the other charts
    – Put you chart dataseries in a table -> chart auto extends with the table https://uploads.disquscdn.com/images/3b3c71cc16e8902a0f635ac4380246204a645d4e95ad63195a315366f9085349.jpg

Chandeep

Popular Posts

© Goodly