Don’t forget to share this post

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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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. 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.
6 Excel Hacks and Tricks Anyone Should Know Format table
6 Excel Hacks and Tricks Anyone Should Know - Format table
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Navigate table
6 Excel Hacks and Tricks Anyone Should Know - Navigate table

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. 1. Click on the dark grey triangle in the top-left corner of your spreadsheet to select all cells.
6 Excel Hacks and Tricks Anyone Should Know Select all cells
6 Excel Hacks and Tricks Anyone Should Know - Select all cells
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Adjust size
6 Excel Hacks and Tricks Anyone Should Know - Adjust size
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Same size
6 Excel Hacks and Tricks Anyone Should Know - Same size
Top Free Excel Finance Templates and Financial Models
Top Free Excel Finance Templates for 2023

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. 1. Type the first item of your series in A1, then click on the “Fill” button on the “Home” tab and select “Series”.
6 Excel Hacks and Tricks Anyone Should Know Series
6 Excel Hacks and Tricks Anyone Should Know - Series…
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Series Columns
6 Excel Hacks and Tricks Anyone Should Know - Series Columns
  1. 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. 1. Select the range of numbers you wish to format as percentages.
6 Excel Hacks and Tricks Anyone Should Know Percentages
6 Excel Hacks and Tricks Anyone Should Know - Percentages
  1. 2. Press Ctrl + Shift + % (Ctrl + Shift + 5) to instantly format them as percentages. The same shortcut will work in both Mac and Windows.
6 Excel Hacks and Tricks Anyone Should Know Ctrl Shift 5
6 Excel Hacks and Tricks Anyone Should Know - Ctrl + Shift + 5
  1. 3. If you use Ctrl + Shift + 4 (Ctrl + Shift + $), you can instantly format your numbers as the default currency for your workbook.
6 Excel Hacks and Tricks Anyone Should Know Ctrl Shift 4
6 Excel Hacks and Tricks Anyone Should Know - Ctrl + Shift + 4

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 Automation Easily Automate Spreadsheet Tasks

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. 1. Go to “View” and click on “New Window”.
6 Excel Hacks and Tricks Anyone Should Know New Window
6 Excel Hacks and Tricks Anyone Should Know - New Window
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Copy of workbook
6 Excel Hacks and Tricks Anyone Should Know - Copy of workbook
  1. 3. Click on “Arrange All” to choose how you wish to arrange the windows. Choose “Vertical” to view them side by side.
6 Excel Hacks and Tricks Anyone Should Know Arrange vertically
6 Excel Hacks and Tricks Anyone Should Know - Arrange vertically
  1. 4. You can now work on different sheets simultaneously.
6 Excel Hacks and Tricks Anyone Should Know Vertical windows
6 Excel Hacks and Tricks Anyone Should Know - Vertical windows

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. 1. Select the cells you want to restrict and click on “Data Validation” from the “Data” tab.
6 Excel Hacks and Tricks Anyone Should Know Data Validation
6 Excel Hacks and Tricks Anyone Should Know - Data Validation
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Any value
6 Excel Hacks and Tricks Anyone Should Know - Any value
  1. 3. Using the “List” option, you can restrict data input to whichever list you choose.
6 Excel Hacks and Tricks Anyone Should Know List
6 Excel Hacks and Tricks Anyone Should Know - List
  1. 4. Select your list using “Source” from wherever it is located.
6 Excel Hacks and Tricks Anyone Should Know Source
6 Excel Hacks and Tricks Anyone Should Know - Source
  1. 5. Once you have selected the list, click “OK”. You can skip the “Input Message” and “Error Alert” to use the defaults.
6 Excel Hacks and Tricks Anyone Should Know Click OK
6 Excel Hacks and Tricks Anyone Should Know - Click OK
  1. 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.
6 Excel Hacks and Tricks Anyone Should Know Select from list
6 Excel Hacks and Tricks Anyone Should Know - Select from list

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!

Conclusion

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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Dec 5 2022