University at Buffalo (UB)
This is an old article.
We keep articles like this online for historical purposes. Please note that it may contain outdated information or broken links. For up-to-date information on this topic, visit our Service Guides.
MS Excel Tips: Taking it to the Next Level
By Marissa Malone
Published May 19, 2015 This content is archived.
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
