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

    • ercramer36 profile imageAUTHOR

      Eric Cramer 

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

    • watergeek profile image

      watergeek 

      5 years ago from Pasadena CA

      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.

    • rfmoran profile image

      Russ Moran - The Write Stuff 

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)