Newcomers to Microsoft Excel on Windows and beyond are often overwhelmed by the vast amount of tools and menus available. Do not be afraid !
Below we have a list of 10 useful tips and tools to help you create the spreadsheet you need (or spice up an existing one). Now you can wow your project team or just make managing your personal budget clear and concise, with these simple yet effective tips.
1. Autofill
When first creating your Excel spreadsheet, entering all your data can take a long time. But Excel is equipped with features to speed up the process. If you enter a series of information, Excel can recognize it and automatically fill in the data in ascending or descending order as needed.
For example, if your column is a list of later dates, enter the first date and left click on the small square in the lower right corner of the cell. While holding down the mouse button, simply drag down and Excel will follow the sequence of dates for as many cells as you drag, filling in the rest of the dates. This can save precious minutes (or even hours over the course of the year).
2. Format cells
If your cells support currency, dates, or even time, Excel can adapt and treat them as such. By right-clicking on a cell (or a group of cells) and clicking “Format Cells”, you can apply a different format of your choice. This saves you from having to manually type symbols such as $ or % depending on the type of formatting you choose, while keeping the data format consistent and tidy. It can also help ensure that your mathematical formulas make sense, for example when adding or subtracting time. There are dozens of options available, so you should be able to find something that meets your needs.
3. Today
Excel spreadsheets are great for tracking deadline management actions. Luckily, Microsoft’s tech wizards have included a simple formula to let your spreadsheet automatically populate with the current date. If you type the formula “=TODAY()” into a cell, it will always display the current date. Open your Excel sheet tomorrow and it will update automatically, and so on. This can be used as a reference point for other cells to help track deadlines. The example above shows a cell with “Today” followed by a list of action deadlines. Just subtract the deadlines from the “Today” date and you can easily determine how much time you have left for your project deadlines.
4. Absolute data $
Sometimes when you want to autofill, you might want to keep some data consistent. Adding a dollar sign ‘$’ to a formula makes the data absolute (always the same). Keeping with the example above, when we look at the “Days Left” formula, we want to keep the “Today” data absolute. So we just add a dollar sign ‘$’ in front of the data we want to keep locked – it will always apply to the number/letter directly to the right of the $. When we next drag to autofill, it will change the data with no $, so the B5 will become B6 but C2 will remain unchanged.
If you’re dragging horizontally instead of vertically, just add the $ in front of the column letter. So C2 would become $C2 (or $C$2 if you want to keep both).
5. Conditional Formatting
This nifty feature lets you set rules for cells to change appearance based on their content. You may want the values to stand out if they are over budget or within a certain time frame. To do this, click the Conditional Formatting option on the Excel toolbar:
There are many predefined options to choose from, such as highlighting the first 10 values, data greater than or less than a set value or within a date range. Or, if you’re feeling brave, you can easily create your own set of rules, even changing cells that contain certain text.
In the example below, I’ve created conditional formatting to change next week’s dates to orange or overdue actions to red, adding a visual aspect that helps prioritize actions.
6. Validation of data
With this feature, you can help minimize potential errors in your Excel spreadsheet. Data validation allows you to limit the types of data that can be entered into a cell. The Data Validation menu can be accessed by clicking on the “Data” tab on the toolbar and then finding the “Data Validation” button:
From here you can set rules to prevent entering the wrong type of data, specifying only dates, numbers, etc., or my personal favorite; create a list of acceptable data entries which can then be selected from a drop-down list when you click on those cells. Data validation even lets you set your own error message if wrong data is entered!
7. Format Painter
Have you ever spent what feels like an eternity turning your spreadsheet into an aesthetic masterpiece, only to add bland, boring new cells and make you feel like you’re back to square one?
Fear not, Excel has a built-in feature called Format Painter that does exactly what it says. You can easily copy and paste your formatting from one part of your spreadsheet to another (including any numeric or conditional formatting you may have added).
First drag your mouse around the format you want to copy, then in the upper left corner of the screen, find the Format Painter button. Clicking it will copy the format, which you can then simply click on the cells you want to apply it to.
Pro tip – double-click the Format Painter icon if you want to apply the same format multiple times or to different areas.
8. Sort and filter
So, you’ve created a huge Excel spreadsheet with tons and tons of useful information. Now you can’t find what you are looking for. With Excel’s sorting and filtering features, you can easily rearrange your data to show you exactly what you need to see. To get started, highlight the top row of the columns you want to include, then look for the Sort & Filter button on your toolbar:
This applies a drop-down menu to each column header, which you can then click on and sort your data ascending/descending by values or alphabetically.
You can also click filters to hide specified data, while matching it to relevant rows. In the example below, I’ve chosen to show only my incomplete actions from my tracker to allow me to focus on what I have left to do. I then filtered by the least amount of time left:
It’s worth noting that the drop-down arrow on the column header changes to indicate that a sort or filter has been applied (this is useful for finding your hidden data when you need to recover it).
9. Graphs and tables
One of the most powerful tools available in Microsoft Excel is the ability to extract your data into a simple yet professional looking chart with the click of a button. This can make even the most mundane data interesting and make it easier to spot trends in your data.
To create a chart, simply select the data you want to include, then click the Recommended Charts button on the “Insert” tab of the toolbar.
This will provide you with a chart or graph that best matches the data you provided, from bar charts to pie charts or even more advanced Pareto charts. Once you get the hang of the basics, you can even play around with adding trendlines or multiple sets of data to the same chart to enhance your presentations even further.
10. Save
Save, save and save again. It really can’t be said enough. The most important tip on this list is to save your Excel spreadsheet frequently. We’ve all been there before… One minute you’re churning out numbers at the speed of light, producing the best results you’ve ever seen, then the next moment it’s all gone. It could be a dead laptop battery, a network error, or the dreaded “Not Responding” message — we’ve all lost work. The larger and more feature-rich your Excel spreadsheet becomes, the more likely it will crash when it calculates your numbers and draws your charts. Often this is recovered, at least partially, by Microsoft Document Recovery, but you don’t want to take that risk.
Top tips for mastering Microsoft Excel
Follow these 10 beginner tips and you’ll be the envy of the office at your next presentation, with your Microsoft Excel spreadsheet singing and dancing. These are just the tip of the iceberg as far as Excel is concerned, but with time and practice they will become second nature and you will be ready for more advanced features to make your life easier and your data more smart.
Mastering Excel using our expert guide, or the rest of Microsoft’s leading productivity suite, is made easy with Office 365 and an all-in-one subscription service with everything you need to … excel.