Finance News & Insights

Saving time in Excel: 12 must-have tricks

So it may not be the best tool … but it’s probably the one you and your finance staffers lean most heavily on.

Excel isn’t going anywhere anytime soon. Which makes any tip or trick to make working in it easier most welcome.

We’ve scoured the web for the best ones your peers are using now. Take a look at these 12 and be sure to pass them to you r staffers, too. You just may find a time-saving treasure or two:

  1. To quickly add up the values of cells, select them and click Alt + =. This will Auto Sum the selected cells and place the value in the cell beneath.
  2. To select an entire row, place the cursor inside one cell and click Shift + Spacebar. To select an entire column, place the cursor inside one cell and click Ctrl + Spacebar.
  3. To display the Style dialogue box, click Alt + .
  4. To insert a blank cell, place the cursor where you’d like to add it and click Ctrl + Shift + +.
  5. When some records (i.e., a row) are duplicated, rather than going through all of the rows to check for duplicates or even Ctr-F-ing, select the rows you wish to search for duplicates, then click the Remove Duplicate button in the Data ribbon.
  6. When you have column headings that would be better used as row headings,rather than copy and paste them all individually, select the range, Ctr-C select start of desired new range, Paste Special –> Transpose. This will turn those X cells into Y cells in the exact same order.
  7. To include all work days for a given range, enter in a date in one cell, enter this formula in the following cell, then drag and fill. (You may need to use the format painter to copy the formatting of your start date and apply it to the WEEKDAY cell. Then proceed with the dragging.) =A1+IF(WEEKDAY(A1)=6,3,1) —> just change the “A1? with whatever cell your start date is.
  8. When working with a big worksheet or referencing the same range of cells frequently, rather than including the range in your formula all the time (and trying to remember it or find them), define the range in the Name Manager (in the Formula ribbon) and then just reference that name rather than range.
  9. To have two different books side by side so you can review or edit both, go to the View ribbon and click on View Side by Side. Once the two workbooks are arranged the way you wish, click on Synchronous Scrolling. Note: This is great if you are comparing two identical documents and looking to make changes to one based on another. This way, when you scroll in one workbook, it will scroll in the other, too.
  10. To get current date with time:
    =NOW() ?shows both, current date and time.
    =TODAY() ?shows only today’s date. (Note: With this trick, =Time (hours, minutes, seconds) value gives you the time you entered in specific format.)
  11. If your data flows from left to right instead of top to bottom, just change the sort orientation from “sort options” in the Data –> sort menu–> Option
  12. To get the calculator in the toolbar:
  • On the View menu, click Toolbars, and then click Customize.
  • Click the Commands tab.
  • In the Categories: list, click Tools, and in the Commands: list, click Custom (The one with the gray calculator graphic).
  • Drag the selected command from the Commands: list to a toolbar.
  • Click Close.

 

 

Print Friendly

Subscribe Today

Get the latest and greatest finance news and insights delivered to your inbox.