University at Buffalo - The State University of New York
Skip to Content

MS Excel Tips: Taking it to the Next Level

By Marissa Malone

Published May 19, 2015

Marissa Malone (UB Student, Class of 2015) has a dream of traveling to all seven continents, and she’s already visited four. Marissa recently graduated, but studied Communication, Spanish, and Management at UB while working for both UBIT and Career Services until May 2015.

Have you always wanted to learn more about MS Excel, but didn’t know where to start? UBIT is pleased to share five of the niftiest tips and tricks to minimize your time and maximize your Excel knowledge base. All of these features are available in MS Excel 2010/2013.

1. See Multiple Workbooks At Once

It’s easy to find yourself switching between two workbooks in order to compare data, but now it’s that much easier.

View multiple worksheets at once or flawlessly shift between different workbooks by using Ctrl + Tab.

OR

You can arrange your windows in order to view all open files at the same time. (As seen in the screenshot)

View --> Arrange All --> Horizontal

2. Add More Than One New Row or Column at a Time

Do you find yourself copying and pasting one column 20 times? Save your time and add 20 rows instantly.

    1.    Select the number of rows you want to add (20)

    2.    Right Click the highlighted rows and choose insert from the drop down menu

3. Navigate an Enormous Spreadsheet with Ctrl + Arrow Keys

Working with a large spreadsheet? Navigate your spreadsheet easier with Ctrl + any arrow key to jump to the edge of the spreadsheet in different directions.  If you want to jump to the bottom line of data, click Ctrl + down.

4. Extract Names and Addresses with a Mail Merge

MS Word pulls names, addresses and other information directly from your Excel spreadhsheet into email meessages, labels, envelopes, or documents being created.

    1.    In MS Word, open a new document

    2.     Select Mailings > Start Mail Merge, click the kind of merge you want to run.

    3.    Click Select Recipients > Use Existing List 

5. Use a PivotTable to Figure Out What Data Really Means

PivotTables are great at collapsing data into useful categories and giving you information about those categories. They're highly customizable, so try clicking 'Insert' and then 'Recommended PivotTables' to get started with some examples.

For examples, if you need to analyze data from a survey where you asked people what TV show they watch; instead of looking through a hundred different responses, a PivotTable will create a new spreadhseet that organizes your data by type of responses instead, so 'Batman' will be one line, 'House of Cards' will be another, and so on, each with a total or whatever other information you want to know. The point is PivotTables take your large, messy data set and turn it into easily accessible information.

Step 1:    Enter Data into Excel

Step 2:    Select the 'Insert' tab to insert 'PivotTable'

Step 3:    Select fields you want to include in the PivotTable

  • Labels will be added to the 'Row Labels' area
  • Values will be added to the 'Values' area

Step 4:    PivotTable Complete