Excel is a wonderfully diverse and complex program, with new features added constantly. What’s more, there are some fantastic shortcuts and tools we can use that help us save time when managing data and make the most of this intelligent tool.
In this article, you will learn 6 Excel hacks and tricks that can save you valuable time and help you avoid mistakes. No matter how advanced you are as an Excel user, there’s a good chance that you’ve missed a few of these.
Excel Hack #1 - Excel Tables
Excel tables are extremely useful, as they allow you to easily use many built-in features. However, the data you receive does not always arrive in this format. What if there was a way to quickly format and navigate your data?
Below, you will find two shortcuts: the first formats your data as an official Excel table, and the second helps you navigate to the corners of that table.
- 1. Select any cell in your data range and click Ctrl + T (Mac and Windows). Remember to check/uncheck “My table has headers” as needed.
- 2. Press Ctrl + '.' to quickly navigate to the corners of your table, clockwise from the top left. This will also allow you to quickly estimate the size of a table you’re not familiar with.
Excel Hack #2 - Adjust Cells Size
When working on a spreadsheet, you often need to adjust all the cells to the same size. With this trick, you can do it in seconds.
- 1. Click on the dark grey triangle in the top-left corner of your spreadsheet to select all cells.
- 2. With all cells selected, click on the separator between any two columns to adjust its size. As shown below, there is a guide to help you get the right size.
- 3. Once you’re happy with the width of the column, let go of the separator and you’ll see the rest of the columns snap to the same size. Repeat this process for the rows if necessary.
Top Free Excel Finance Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.READ MORE
Excel Hack #3 - Fill Consecutive Numbers
At some point, you may find that you need to type in a long list of consecutive numbers into your spreadsheet. While dragging the list down may work in some cases, it’s not very practical for a long series. Using the “Fill” button, you can quickly and easily create both simple and complex series.
- 1. Type the first item of your series in A1, then click on the “Fill” button on the “Home” tab and select “Series”.
- 2. If you want to fill the series down the column, make sure you choose “Columns” as below. You will get the numbers from 1 to 10000 in intervals of 1. Click “OK” to continue.
- 3. As you can see above, there are multiple options to create more complex or non-linear series. You can find out more about these settings on the Microsoft support page.
Excel Hack #4 - Percentage and Currency Shortcut
If you frequently need to format numbers as percentages or currencies, this shortcut will save you valuable time.
- 1. Select the range of numbers you wish to format as percentages.
- 2. Press Ctrl + Shift + % (Ctrl + Shift + 5) to instantly format them as percentages. The same shortcut will work in both Mac and Windows.
- 3. If you use Ctrl + Shift + 4 (Ctrl + Shift + $), you can instantly format your numbers as the default currency for your workbook.
Excel Automation: Easily Automate Spreadsheet Tasks
Many Excel tasks are repetitive and time consuming. Luckily there are several ways to automate tedious tasks in Excel. Here’s how.READ MORE
Excel Hack #5 - Switch Between Multiple Sheets
When using a workbook with multiple sheets, you may find yourself constantly switching from one sheet to another. This can become very tedious and can lead to mistakes. Instead, open a new window in the same workbook to work on both sheets at the same time.
- 1. Go to “View” and click on “New Window”.
- 2. This will open another copy of the workbook. As you can see, Excel adds a number after the workbook name, indicating which copy it is.
- 3. Click on “Arrange All” to choose how you wish to arrange the windows. Choose “Vertical” to view them side by side.
- 4. You can now work on different sheets simultaneously.
Excel Hack #6 - Data Validation
Manual data entry is still as necessary as it is error-prone. It’s disappointing enough when you notice the mistake and have to redo work, but the real problem is that you may not notice in time. Excel’s “Data Validation” allows you to define exactly what can be typed into a cell, as well as customize the error message to help users input the correct data.
For example, you may want to add a column with the type of employee, for which there are two options: full-time or part-time.
- 1. Select the cells you want to restrict and click on “Data Validation” from the “Data” tab.
- 2. In the “Settings” tab, you can choose from a variety of validation criteria, to ensure that only the right type of data can be used.
- 3. Using the “List” option, you can restrict data input to whichever list you choose.
- 4. Select your list using “Source” from wherever it is located.
- 5. Once you have selected the list, click “OK”. You can skip the “Input Message” and “Error Alert” to use the defaults.
- 6. Now it’s only possible to type in or select the values from your list. There’s no need to worry about misspelled words, inconsistent use of caps, or even imaginary options.
Bonus: Google Sheets Hack - Automate Your Data and Tasks
Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.
Using Layer, you can:
- Share & Collaborate: Automate your data collection and validation through user controls.
- Automate & Schedule: Schedule recurring data collection and distribution tasks.
- Integrate & Sync: Connect to your tech stack and sync all your data in one place.
- Visualize & Report: Generate and share reports with real-time data and actionable decisions.
Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!
Although the most recent version of Excel integrates powerful tools that can help you complete any task, there are many hacks and tricks that can speed up your work, especially for manual tasks that can’t be avoided.
By now, you should now know of 6 new, easy hacks that you can use to speed up everyday tasks on Excel spreadsheets, such as data entry, formatting, and visualization.