ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Excel Time Saving Tips

Updated on March 24, 2014
Time Saving Tips
Time Saving Tips | Source

Excel is a powerful tool that is used for an endless amount of applications. As an accountant, I use Microsoft Excel for all kinds of different things including financial reporting, analyzing data, creating charts, personal finances, and I use the drawing tools to map out my woodworking projects. It is funny that the more ways that I learn to use Excel; I am still able to find new ways to become more efficient with it. Here is my list of Excel tips and tricks that will save you time.

Keyboard Shortcuts

One of the best tips for MS Excel that I can give you is to learn to use keyboard shortcuts because they are a great way to save time. It is much quicker to use a combination of keys rather than taking your hand off of the keyboard and using the mouse to do it. Here are the shortcuts that I find to be the most useful:

CTRL +

Keystroke
Description
A
Select All
B
Bold
C
Copy
F
Find
H
Find and Replace
I
Italics
K
Insert Hyperlink
N
New Workbook
O
Open Workbook
P
Print
S
Save a File
U
Underline
V
Paste
W
Close Workbook
X
Cut
Y
Redo Undo
Z
Undo Last Action

Shift + End +

Keystroke
Description
Page Up
Switches to the Worksheet Tab left of the current tab
Page Down
Switches to the Worksheet Tab right of the current tab
Up Arrow
Highlights from the current cell to the top of the list
Down Arrow
Highlights from the current cell down to the end of the list

Function Key Shortcuts

F Key
Description
F1
Help
F2
Edit Formula
F4
Repeat Last Action
F4
Make a Cell Absolute, Relative, or Mixed
F9
Recalculate
Click thumbnail to view full-size
Excel's RibbonExcel OptionsCustomize the Ribbon
Excel's Ribbon
Excel's Ribbon | Source
Excel Options
Excel Options | Source
Customize the Ribbon
Customize the Ribbon | Source

Customizing the Ribbon

Customizing the ribbon in Excel is a great way to become more efficient. The ribbon is located either above or below the toolbar. It came in to existence starting with Excel 2007, before that you could customize any toolbar or create your own. My advice is to add the buttons that you most commonly use to the ribbon. Click on the Microsoft Office button in the top left hand corner of the screen and select “Excel Options.” On the left hand side of the popup window, click on “Customize.” From here, you can add any button that you want to the ribbon. I suggest adding the following plus to your ribbon:

  • New Workbook
  • Open Workbook
  • Save Workbook
  • Save As Workbook
  • E-Mail
  • Set Print Area
  • Print Preview
  • Quick Print
  • Insert Sheet Columns
  • Insert Sheet Rows
  • Delete Sheet Columns
  • Delete Sheet Rows
  • Hyperlinks
  • Record Macro
  • Insert PivotTable
  • Freeze Panes

Click thumbnail to view full-size
Excel Options - Show Developer TabDeveloper TabRecord a Macro
Excel Options - Show Developer Tab
Excel Options - Show Developer Tab | Source
Developer Tab
Developer Tab | Source
Record a Macro
Record a Macro | Source

Macros

Macros are another great way to save time in Excel. Macros are easy to record and use. A macro is a task or a series of tasks including any keystroke or mouse click that are performed all together. It can than be assigned to run automatically when the spreadsheet it opened or by using a command button.

Have you ever created a macro before?

See results
Vlookup Example
Vlookup Example | Source

Vlookup

Using Vlookup to pull data from a table to another area of your spreadsheet or to a new spreadsheet can be a real time saver. Vlookup does have some limitations. First, what you are looking up must be the same in both places. Second, Vlookup will only return the first occurrence of what you are trying to lookup. Finally, Vlookup can only return results in the column being looked up or the columns to the right of it. The Vlookup formula looks like this:

=Vlookup([what is being looked up],[table with column matching what is being looked up all the way over to the column that you want to return the results from],[number of columns to the right of what is being looked up],[true or false])

Most Vlookup formulas will end in false. The false statement tells Excel to return an exact match or return a #Error message. The use of true will find an exact match or the next closest match.

Format Painter
Format Painter | Source

Format Painter Button

The “Format Painter” button is a great way to copy the format of a cell or group of cells and paste the format over a desired area. All you have to do to use the “Format Painter” button is to highlight the cells with the formatting that you wish to copy and then click the “Format Painter” button. The highlighted cells will look like you copied them. Next, highlight the area that you want to paint the formatting to. One word of warning is that once you let go of the mouse it will paint whatever cells that you have highlighted whether you have highlighted the whole area or not. If you do make a mistake, hit “CTRL + Z” to undo it and repeat the process to try again. The “Format Painter” button is located on the “Home” tab on the left hand side.

Add a hyperlink in Excel
Add a hyperlink in Excel | Source

Hyperlinks

Hyperlinks are an invaluable resource in a spreadsheet. They make navigating a large workbook or worksheet much easier. The use of hyperlinks in Excel allows you to link to external documents, internal parts of your workbook or worksheet, or websites. I use them regularly as part of a table of contents for my financial reporting template at work that has well over 100 different tabs. I also use them to link to external files that are related to the current worksheet. To insert a hyperlink, use the keyboard shortcut “CTRL + K”. Alternatively, you can also right-click in the desired cell and select “Insert Hyperlink.” A box will popup and you can change the name that will appear as the hyperlink and add the website address or select the tab and the cell reference inside your current document.

Worksheet Linking Example
Worksheet Linking Example | Source

Linking Data Between Tabs

For our financial reporting template at work, we enter all of the data into the back of the file and pull that information forward by linking to the data tabs in the back of the file. This will save you from having to do excess data entry in Excel. For most of those tabs, all I had to do was put in a simple formula to grab that data and pull it forward. I did this by entering an equal sign and then going to the appropriate data tab(s) in the back and click on the cell or cells that I wanted to pull forward. It is important to make sure that you check the data after you enter the formula to make sure that it is right.

Find and Replace Popup Box
Find and Replace Popup Box | Source

 Using Find/Replace

Another one of my favorite time saving measures in Excel is to use find and replace. I find that it is great when you are creating a template and it is quicker to copy over an existing linked tab and use find and replace to change the tab where the formula points. You do have to be careful when using this because you can easily change things that you do not want to be changed. It is best to highlight the area that you want to change and then hit “CTRL + H” to bring up the find and replace dialogue box. Type in what you want to find and then type in what you want to replace it with in the corresponding box. Leave the “Within:” drop down box as “Sheet” unless you want to find and replace all throughout your workbook. Searching by rows or columns does not seem to make any difference. I normally leave the “Look in:” drop down box as “Formulas”, but if I am searching for a numeric value and do not find it, then I may try changing this to “Values.”

I hope that these tips for Microsoft Excel will help you to save time. One of the great things that I love about Excel is that the more that I am in it, the more ways that I find to become more efficient.

Comments

    0 of 8192 characters used
    Post Comment

    • rfmoran profile image

      Russ Moran 4 years ago from Long Island, New York

      Wow what a great hub. Thank you so much for making life a bit easier. I love excel and now I love it more. Voted up useful and shared with my 8500 twitter followers.

    • watergeek profile image

      watergeek 4 years ago

      Is there a F3 function key shortcut? I love Excel too. I don't have it on my Mac, but many of these functions work for NeoOffice. Thanks for the tips.

    • ercramer36 profile image
      Author

      Eric Cramer 4 years ago from Chicagoland

      F3 does have a function keep shortcut. If you have defined a Named Range in your worksheet, F3 will bring up a popup box asking if you want to Paste Name. I tried it and it pasted the Name of the Range and what cells that the Range Covered.

      Shift + F3 will bring up an Insert Function Wizard menu where you can insert whatever function that you would like.

    Click to Rate This Article