Top 10 Excel Tips and Tricks

Microsoft Excel can be one of the most valuable tools available to use on a variety of projects. Once you grasp the basics of Excel, there are several key tips that can make using Excel much easier. The following list is ten great tips you can use to make the most out of this program.

1.) Custom Lists

Creating custom lists is great for repeated information. If you have data that you need to continuously repeat, Excel offers an “auto fill” option that helps minimize the amount of times that you have to re-type the information. For example, if you were creating a student roster and needed to list student names repeatedly, you would go to the Microsoft Office button and click on Excel options, where you would choose “Custom List”. You can choose this on a new or existing spreadsheet. After this, click on the “Popular Tab” (it will say “Change the most popular options”), followed by clicking “Edit Custom List”. Proceed to type the list of names, placing a comma between each. Once the list is complete, click Add and then OK. On the worksheet, type a name from the list, choose AutoFill, and the entire list will fill in.

2.) Linking Data

Excel offers the convenient option to link related data from one worksheet to a separate summary page if you choose “Paste Special”. First, copy the formula you wish to use and then on another worksheet choose which cell you want the link placed. Next, right click in the same cell, choose Paste Special and in that box click on Paste Link.

3.) Sorting Data

Don’t forget about the sort & filter option. This serves as a great shortcut and can make filtering your data very simple.


4.) Shortcuts

There are several shortcuts you can use to quickly navigate and make changes on a spreadsheet. Here are just a few of the convenient shortcuts:
*To apply a time format with hours, minutes, or AM and PM, press Control, Shift, and @
*To enter the current time, click Control, Shift, and :
*To enter today’s date, click Control and ;
*To switch to the next workbook’s window, click Control and F6
*To put a outline border to selected cells, click Control, Shift and &
*To go back and forth between cell values and formulas, click Control and ‘

5.)Filtering Data

This is useful for quickly finding a subset of data in a spreadsheet’s column. You can put a filter on columns that have text or numbers and if you decide to remove the filter simply click on the filter symbol on the column header and select Clear Filter under “Column Name”. It is helpful to remember that a table can go back to cells and the formatting will remain. To do this choose Table, click on Table Tools Design Tab, and choose Convert to Range.

6.) Putting data into a Table format

In order to quickly analyze a group of data, you can turn a group of cells into a table. Several table styles are available, or create a custom style. To make a data table, choose Range of Cells, Home, Styles Group, and then choose to Format as a Table. You can then add elements to it and adjust it under the Table Tools Design Tab.

7.) Creating a Drop Down List

This function lets you make a “quiz” by placing a drop down list in a particular cell. To do this, go to “Data Ribbon” and choose “Data Validation”. When you create the cell’s list, there is an arrow next to the cell. You can then click the arrow to choose the correct response or entry.

8.) Looking Up Specific Data In A Spreadsheet

Using a Look Up function will allow you to search the document for specific information. Once you choose this function, type in VLookUp followed by what you’re searching in parenthesis, and what range of cells to look in. For example, if you were searching for the name John it would look like this: Function=VLookUp(John,A1:D13,FALSE). Typing FALSE tells Excel that an exact match is required.

9.) Hiding Information So That It Doesn’t Print

If there is info on your spreadsheet that you don’t want printed, simply select the row or column that you don’t want printed, then right click on the highlighted information (or the highlighted selection of columns or rows you chose) and click Hide.

10.) Highlight Alternating Rows For Easier Viewing

To do this, highlight the range of information you want to apply formatting to. Then choose Format, then Conditional Formatting and a box will appear. Choose Formula Is in the drop down list, and in the second box type what info you want highlighted. It should look something like this: =MOD(Row(),2)=0. Then click the Format Button to bring up the Format Cells box. Choose the Patterns tab and select a color for the range you want colored.

Using these tips can help you use Excel more efficiently and soon you’ll be breezing through Excel projects like a pro!

One comment

  1. wschloss says:

    #2 is especially useful for charting/graphing

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.