Excel is one of the most useful tools out there, but some find it complex. Here are some simple tricks to help students excel at using this handy spreadsheet tool. If you don’t have Excel, find out if you and your students are eligible to download it free as part of the Office 365 Pro Plus Benefit— click here to see if you are eligible.
1. Never lose sight of row headers by freezing panes
Freezing Panes is one of the most useful tricks in Excel—with this you can always view your labels for different rows and columns, no matter how much you scroll.
- If the pane you wish to freeze is the first row, simply go to View → Freeze Panes → select ‘Freeze Top Row.”
- If it is not the top row, simply click on the cell below the pane you wish to freeze (if you want to freeze row 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
Simply click New, then search for “GPA Tracker” in templates —the GPA Tracker template allows students to input their own grades for different types of assignments in each class to get an idea of how they are doing. If your students are on tablets, here’s a tutorial on how to track GPA in Excel for iPad.
3. Move back and forth between spreadsheets quickly without clicking anything.
If you are working with multiple spreadsheets and need to move between them, press Ctrl+Tab to move back and forth between two spreadsheets, and 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 row of numbers, for example “5554443333”, and you want to change the format to a phone number such as “(555) 444-3333.” Instead of manually typing it out, Flash Fill can do this for you automatically by detecting patterns in your formatting, and 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 not have it be scattered when you receive it? Excel Survey (found in Excel Online) allows you to create a survey quickly and easily, then compiles the information in real time as it 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 put your cursor over the border—it will change into a crossed arrow icon, allowing you to drag to move the column freely.
7. Select all with one click
Ever wonder what that little triangle is at the top left corner of your spreadsheet? You can click on it and quickly select all the cells with open simple click.
8. 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” + the arrow key in the direction you want to go. For example, “Ctrl+ →” will take you to the rightmost point of your data, and “ctrl+ ↓” will take you to the bottom line of the data.
9. Transpose Data in Rows to Data in Columns, and Vice Versa, with two clicks
Sometimes you realize the data you had displayed horizontally would make more sense displayed vertically. Instead of copying and pasting everything one by one, copy and paste all the data you’d like copied, then click Home→Paste→Transpose Icon, and the data will display in the new format.
10. Find out the average, count, and sum without any formulas or clicks
Did you know that if you select a set of numbers, the average, count, and sum automatically display without you doing anything? Excellent.
11. Combine two chart types by creating a combo chart and adding a second axis
What happens when you have two different types of data you want to show in a single chart? Excel makes it easy. Simply select the data you’d like for your chart, press the insert tab and click recommended charts, then click all charts tab and select the combo category. If the two sets of data have different scales, simply check the “secondary axis” box for the scale you’d like to add and click ok. Find out more in this in-depth tutorial.