The Student Guide: 10 Best Microsoft Excel Hacks You Need To Know!
Excel is a data, planning and organisational dream tool. It doesn’t matter what your chosen subject is, you will definitely be using Excel at some point during your student career.
There are so many things that Excel can do – too many for one blog post!
Here’s our top ten Microsoft Excel hacks that will be useful well beyond your student days.
VLookup is one of the best excel hacks for any student!
Even if you aren’t tasked with diving deep into data, it can transform the way you review tables and set out your spreadsheets.
VLookup stands for Vertical Look Up and allows the user to search for a value within a table and show the value within another table. As often is the way with Excel, an example is the best way to show how these formulas work.
In the two tables below, Product List and Customer Info, we can complete the Contact Preference column within the Product table by setting VLookup to search using the Customer ID section.
To get started, select the empty field and go to the Lookup & Reference drop-down within the Formulas tab. From here, you choose VLookup and plug in the relevant fields you wish to search across. Not just for your student years, VLookup is amazing for budgeting, organising events and market research.
Filters are a great way of reducing the amount of data on screen and making it more manageable to read.
They are ideal for making sure the only information on screen is relevant for your requirements.
Select the column you wish to apply a filter to, and then using the Filter option within the Data tab, Excel will automatically allow you to filter any information within the specific column.
In the example below, we are now able to filter the Contact Preference column to only view a specific set of customers.
3. Recommended Charts
Recommended Charts are a great way of visualising student research data and helping any other users interpret what may otherwise be lengthy lists of names and numbers.
This is particularly useful if you are preparing to reference your data within a presentation.
This is also one of the easiest tools within Excel; press Recommended Charts on the Insert tab and let Excel do the rest.
All you need to do is then find the chart which represents your data in the clearest manner. It’s perfect for creating a quick snapshot of your project.
Autofill excel hacks is a huge time saver! You need to know about it ASAP.
Excel will recognise a pattern between selected numbers that allows the user to automatically fill in the remaining fields in the same manner.
Just enter the first two numbers then select the fields and using the small black box in the bottom right corner drag the fields until you have the necessary number of entries.
5. Add More Than One Row/Column At Once
This is one which will come in useful particularly when you have established tables/data and need to insert new columns and rows.
If you right click a highlighted row or column and click Insert, it will insert a new blank row/column for you above or to the left respectively.
To avoid having to repeat this over and over for instances where one row is not enough, using the side/top bars and your shift key select multiple rows and then right click and Insert.
This will automatically add the number of columns or rows that you have highlighted.
6. Pivot Tables
There are entire books dedicated to Pivot Tables and this short intro won’t do it justice. But we’ll give it a go!
Pivot Tables are super beneficial for reorganising your student project data and presenting it differently without impacting or changing the data within your tables. These are extremely useful for extracting data from large data sets.
In order to generate a Pivot Table, select Pivot Table from the Insert tab. Excel has a recommended Pivot Table option which is a good place to start if you are a newbie.
The tables will open in a separate sheet within your Excel workbook and can be as simple or as complicated as you want to make them.
The best thing to do is to play around and experiment with the new ways to format your data.
7. Freeze Panels
Large data sets can cause LOADS of problems and keeping track of what you are looking at is key. This can be particularly difficult if you are having to scroll through pages and pages of data.
A super easy solution is to freeze your heading/description fields so that they remain in place whilst scrolling.
The first step is to select the fields you want to freeze then use the Freeze Panes button on the View tab. Now you can scroll with impunity.
If you find the need to change up which sections you see consistently, unfreeze the panes with the same drop-down and repeat the process.
8. Remove Duplicate Data
Duplicate data is a common problem within larger data sets and easily getting rid of it is one of the best excel hacks you’ll ever know.
As such, sifting through and deleting duplicate data is time consuming and also an unnecessary task.
You could be spending your valuable student time on something way more productive, so here’s a nifty trick.
Select the column or row you suspect has duplicates, go to your Data tab and select the Remove Duplicates option. This will open a new window allowing you to review the duplicate data Excel has found and select any that require removal.
9. Conditional Formatting
This is another huge area of Excel with almost endless possibilities. There is so much you can do with Conditional Formatting, from changing a field to red if the date within it has passed to inserting an entire graph into each cell.
The Conditional Formatting drop-down on the Home tab is the starting point, click and check out the best options available to you.
10. Auto-Delete Blank Cells
When merging and transferring data from other sources, cells within your spreadsheet will often be blank. These will need to be removed in order to ensure your data is as accurate as possible.
You can achieve this by utilising the Filter tool we mentioned earlier and filter the row/column you believe has blank cells. Filter for blanks, select all the cells and then delete from the Home tab.
Excel hacks can make your student work-life pretty excellent. These 10 nifty tricks are bound to make your life easier and far more productive so you can excel in other areas! Yes, that was a pretty terrible pun. Give our hacks a go and let us know how you get on our socials as well as your own neat tips and tricks you want to share! All that Excel chat got you thinking about data and business? Check out our blog on how to start an online business here.