Whether you’re studying journalism or engineering, you can’t escape from Microsoft’s Excel. With so many different functions, almost anyone can find a use for it. But at the same time, so few have mastered it. While the sheer ability of the application can be intimidating, there’s no doubt that knowing how to use Excel gives any student an edge in the workplace. The current version, Excel 2016, is available as part of Microsoft Office 2016 – available to students for free.
We’ve come up with 9 essential Excel tips for university and college students:
1. Recommended Charts
The newly added Recommended Charts feature let’s you quickly create a chart that’s just right for your data. This feature saves time so you don’t have to test out every chart before picking the best one.
Once you’ve entered your data into Excel with column headers, go to Insert > Recommended Charts. A window will pop-up that will show you the best options for your data. If you don’t see a chart that you like, click on the All Charts tab at the top to view all available chart types.
2. Pivot tables
Pivot tables are arguably one of the most powerful and underrated Excel tools. Especially useful for big data sets, pivot tables are a great way to summarize, analyze, and explore your data. The best part is that none of this requires writing a single formula.
Go to Insert > Pivot Table and select your data range. A window slides open on the right where you can select your fields in the top half of the sidebar and generate the table in the bottom half.
Pro tip: Similar to Recommended Charts, there’s also a Recommend PivotTable option. With this feature, Excel suggests layouts based on the data provided. If you’re just starting with pivot tables, this makes a great starting point.
When you want to swap your rows into columns (or vice versa), the transpose feature lets you simply do this without manually copying and pasting each individual header. Magic, isn’t it?
To transpose a column into a row (or vice versa), begin by right clicking it, and select “Copy”. Then, select the cells wheere you want your first row or column to begin, right-click on that cell, and select “Paste Special”. A window will pop-up – check the “Transpose” box at the bottom right and click OK. Now your row is now a column or vice versa.
If you’re working across different sheets and workbooks, VLOOKUP will allow you to combine data to create reports and summaries. If you need to combine data sets, VLOOKUP is your go-to formula. When using this formula, make sure you have at least one column that appears identically in both places.
For example, you might have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their school names in another – but you want the names, email addresses, and company names to appear in one place.
The magic formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
5. Quick Analysis
The Quick Analysis feature is one of the newer features in Excel. It used to take a bit of work to analyze your data but Quick Analysis makes it, well, quick. You can instantly create different types of charts, including line and column charts, and sparklines. You can also apply a table style, creative PivotTables, quickly insert totals, and apply conditional formatting. In case you’re not sure which one to use, here’s a breakdown:
- Formatting lets you highlight parts of your data by adding things like data bars and colors. This quickly lets you see high and low values, among other things.
- Charts shows you recommended charts based on the data you have selected.
- Totals lets you calculate the numbers in columns and rows. For instance, Running Total inserts a total that grows as you add items to your data.
- Tables make it easy to filter and sort your data.
- Sparklines are tiny graphs that you can show alongside your data.
To access the Quick Analysis menu, select your data and click on the icon that pops up in the bottom right corner.
Autofill is a huge time saver that anyone can use. Excel notices patterns in your dataset and automatically completes the rest. For instance, if you want to include only even numbers up to 100, you can start by inserting 2, 4, 6, and 8 into cells, hovering over the bottom right of your data selection (you should see a black cross), and dragging downwards until you have the data you need.
7. Conditional formatting
Conditional formatting makes it easy to identify trends, outliers, or get a general sense of your data at a glance. Specifically, it lets you change a cell’s color based on the information in the cell. For example, you can highlight numbers that are below average in your spreadsheet.
To apply conditional formatting to your data, first highlight the cells that you want to apply. Select “Conditional Formatting” in the Home tab and choose an option from the dropdown menu. A window will pop-up that’ll prompt you to provide more information about your formatting rule. Click OK once you’ve done and you should see your results right away.
8. Dollar signs
You can use dollar signs in a formula to make sure that a specific column and row stay the same – even if you copy and paste that formula into other cells. In other words, dollar signs let you change your Excel formula from a relative one to an absolute one. To make this change, simply add dollar signs before and after the row and column values, like this: from (=A5+C5) to (=$A$5+$C$5).
Filters are a great way to look at specific data within a large data set. With filters, you can reduce your data to only look at what fits into certain criteria (without changing the original data set). For instance, in the example below, I might only want to look at students in Ottawa – filtering lets you do that. In Excel, you can add filters to every column and then choose which cells you want to view at the same time.
To begin filtering, select your data set, Home > Sort & Filter > Filter. Arrows will appear next to your headers. Clicking on these will reveal a drop down menu where you can filter to your heart’s content.
You might also like:
Featured photo: Rawpixel.com via Shutterstock