Excel is one of the most useful tools out there, but some find it complex. Here are a few simple tricks to help students successfully use this handy spreadsheet tool. If you don't have Excel and are a teacher or student, find out if you and your students are eligible for a free download as part of the Office 365 Pro Plus Benefit.
1. Lock your view of row headers by freezing panes
Freezing panes is one of the most useful tricks in Excel-you can view your labels for different rows and columns, no matter how much you scroll.
- If the pane you want to freeze is the first row, simply go to View à Freeze Panes à select Freeze Top Row.
- If you want to freeze a different row, simply click on the cell below the pane you want to freeze (if you want to freeze rows 3 and up, select a cell in row 4), go to View à Freeze Panes à Freeze Panes.
2. Give your students the ability to keep track of their assessment through Self-Grade Computation
The GPA Tracker template helps students track their progress in class by enabling students to input their grades for different assignments. Simply clickNew, then search for GPA Tracker in templates. If your students are using iPads, here's a tutorial on how to track GPA in Excel for iPad.
3. Alternate between spreadsheets quickly without clicking anything
If you are working with multiple spreadsheets and need to move between them, press Ctrl+Tab to alternate between two spreadsheets, and press Ctrl+Shift+Tab to move to the previous spreadsheet.
4. Use Flash Fill to make formatting tasks a breeze
Let's say you have a set of numbers, for example 4255550333, and you want to change the format to a phone number, (555) 555-0333. Instead of manually typing it out, Flash Fill can do this for you automatically by detecting patterns in your formatting, and Flash Fill works best when your data has some consistency. Works as well with formatting names, dates, and postal codes.
5. Use Excel Survey to collect information directly into an organized spreadsheet
Need to collect data from others and keep the data organized? Excel Survey (found in Excel Online) allows you to quickly and easily create a survey. Excel Survey compiles the information in real time as data comes in. Great for group projects and surveys.
6. Move entire columns of data easily and quickly
Simply highlight the column you want to move and pause your cursor over the border-the cursor will change into a crossed arrow icon, which allows you to drag to move the column.
7. Select all with one click
Ever wonder what that little triangle is at the top left corner of your spreadsheet? Click the triangle to select all the cells in the spreadsheet.
8. Press Ctrl + Arrow to move to different corners of the spreadsheet
Working with a particularly long and extensive set of data? No need to scroll-simply press Ctrl + arrow key in the direction you want to go. For example, Ctrl + right arrow will take you to the rightmost point of your data, and Ctrl + down arrow will take you to the bottom row of the data.
9. Transpose data in rows to data in columns, and vice versa, with two clicks
Sometimes you realize the data you displayed horizontally would make more sense displayed vertically. Instead of copying and pasting everything one by one, copy and paste all the data you want copied, then on the Home tab click Paste and then click the Transpose icon and the data will display in the new format. Learn more about switching and transposing.
10. Display the average, count, and sum without any formulas or clicks
Select a set of numbers and the average, count, and sum display automatically.
11. Combine two types of charts by creating a combo chart and adding a second axis
Excel simplifies displaying two types of data in a single combo chart. Select the data you want for your chart, click the Insert tab and click Recommended Charts. Next click the All Charts tab and select the Combo category. If the two sets of data have different scales, simply select the Secondary Axis check box for the scale you want to add and click OK. Find out more in this in-depth tutorial.