Tech Gear for Productivity Tools and Hacks to Boost Your Workflow

By Isabella Storyclaire

last updated September 17, 2024

Contributions sourced from

25 Excel Hacks and Tricks to Boost Your Productivity in 2024

Microsoft Excel offers many advanced shortcuts, formulas, and functions to learn and apply.

Whether you are an Excel beginner or possess moderate Excel tips, here are Excel hacks to evolve your knowledge and amp up your Excel game.

25 Excel Hacks and Tricks to Boost Your Productivity in 2024

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.

What is Excel?

Microsoft Excel is a data analysis, visualization, and data validation spreadsheet software that helps business professionals store data and track critical business datasets with simple to advanced formulas.

Excel uses spreadsheets to optimize businesses data operations without affecting your data integrity.

Regardless of the type of industry, all departments, including sales, marketing, finance, operations, technology, and human resources use Excel.

While the use cases differ for each business department, knowing practical Excel hacks and tricks to generate your chosen report, dashboard, and statements will help.

25 Excel Hacks for Every Use Case

If you are reading this, you must be aware of the Excel basics, like:

Adding, subtracting, multiplying, and dividing numbers
Adding or deleting multiple rows, all the columns, one cell, and multiple cells
Adding colors, highlighting numbers, blank cells, rows, and columns

Since most people know how to carry out these functions, we wont bore you with that. Lets discuss the advanced Excel hacks to take your skills to the next level.

We have segregated the Excel hacks and tricks into three categories:

Keyboard shortcuts, formulas, and functions: For all the functions and formulas to conduct extensive and calculated calculations within minutes
Productivity boosters: To help you convert your Excel sheets into a productivity machine
Optimizing spreadsheets: Help you optimize your spreadsheet with minimum work and get better at data validation

Keyboard shortcuts, formulas, and functions

Below are the keyboard shortcuts every Excel enthusiast must know:

1. Entering the number as a text

In situations where you want to feature numbers in a readable format, use this shortcutadd an apostrophe () before the number.

2. Selecting a portion of data at once

When dealing with a huge dataset, if you want to select a particular portion of the data:

Add the first cell or fill up one cell
Press the Ctrl Shift buttons together and scroll down
Click the last cell

3. Open more than one file at once

Want to open multiple Excel files in one go? Heres how to do so:

Click the File tab and then Open in the top data tab
Hold the Ctrl button and select all the files
Now click on Open

4. Autofill technique

Tired of right-clicking every time and clicking on Paste Special to add missing information?

Excel sheet fills in missing information in a data series. Suppose you make a series of even numbers and fill in the first three numbers in an entire column.

Select the first three numbers
Drag your cursor down to the bottom right corner until a + sign appears
Now drag the cursor down based on the serial numbers you are looking for

5. Simplify data visibility with the Transpose feature

Suppose a long column filled with large datasets confuses you. A great way to simplify your view is to convert these columns into rows (and vice versa). This will help you organize your entire worksheet and establish straightforward visibility.

Why go for the same old route of using Paste Special to create the rows manually when the Transpose feature does the job?

Select the rows/columns/multiple cells you want to transpose
Right-click to Copy these datasets
Select the columns/rows where you want to see the new data view
Now right-click again and click on Paste Special
In the Paste Special window, scroll down to check the Transpose box

6. Create simple charts and graphs

Creating simple marketing and sales dashboards is easy with Microsoft Excel. An Excel sheet allows you to create simple bar charts, line graphs, and pie charts for selected datasets. Add a table within Excel or copy the datasets and Paste Special to create charts.

Select the datasets in multiple cells
Click Insert followed by Recommended Charts
In the Recommended Charts window, you will find all the chart types Excel recommends for the highlighted datasets
Pick the most convenient chart type and click on OK
Use Chart Elements, Chart Styles, and Chart Filters to customize your charts by changing axes and adding colors and data labels

The Save as a Template feature lets you save chart templates for future use instead of creating them from scratch each time.

Saving your Excel Chart as a Template via Office Watch

7. Add filters

If users want to view a particular row or column, Excel allows them to filter out the remaining data from the spreadsheet.

To do this:

Click on the Sort and Navigation bar in the top data tab
In the Drop-down window, select how you want to filter the datasets, like A to Z or Z to A, or customized sorting

8. Select unlinked cells and format cells

Want to select multiple cells from different columns or rows instead of double-clicking Paste Special over and over?

Click on the Ctrl key and select the specific cells.

Another great Excel hack is to format cells by:

Right-click in the Home tab
Click on Cell Styles
Click on Modify followed by Format to format cells and change the content as you want

The Format Cells feature improves the clarity and readability of data presentation.

9. Create a personalized shortcut menu

If you are a dedicated Excel user and open Excel files daily, customize your Excel ribbon so you dont have to look for shortcuts every time.

Go to File and select Options
Now click on the Quick Access Toolbar
Add all the functions that are a must for your daily Excel operations
Click Save

Pro tip: Consider using pivot tables to summarize entire data quickly.

To create pivot tables, select any one of the blank cells, click on Insert, and then Click on Pivot Table.

A pivot table and a personalized shortcut menu will help you get a detailed understanding of a complicated dataset. Adding a pivot table to Excel reports is common for sales and marketing professionals.

10. Five major Excel functions and their applications

Here is a quick glimpse into the five most useful Excel functions and how to use them:

IF: This function performs a logical test to help the user return to one value if the condition is true or to the other if the condition is false

Syntax IF(logical_test, value_if_true, [value_if_false])

INDEX: The function returns to a particular value or one of the first cell references from a data array

Syntax INDEX(array, row_num, [column_num])

VLOOKUP function/HLOOKUP function: The VLOOKUP function lets you search for data in the rows of your spreadsheet, and HLOOKUP allows you to search for data in the columns

VLOOKUP Function Syntax -VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))

HLOOKUP Syntax VLOOKUP(lookup value, table array, row number, Approximate match (TRUE) or Exact match (FALSE))

MATCH: This Excel function searches for a particular item in multiple cells and returns the item in a relative position within the same range

Syntax MATCH(lookup value, lookup array, [match type])

COUNTIF: This function counts the number of cells that meet criteria or have the same value

Syntax COUNTIF(range, criteria).

11. Eliminate duplicate datasets

Long-range datasets tend to have duplication. For example, if you are trying to organize all datasets related to newsletter subscribers within an Excel sheet and the same person subscribes twice, there will be the same email address twice.

To identify duplicate datasets, follow these Excel tips:

Select the entire row or column in question
Go to Data in the Home tab
Select Remove Duplicates

12. Merge two or more arrays

Business teams often deal with more than one long table array of datasets highlighting the same items. By merging these arrays into one data range, you will save time, and segmenting large volumes of datasets will be more straightforward.

One of the Excel tips to merge cells is to use the CONCATENATE function.

Syntax CONCATENATE(text1, [text2], )

13. Compose texts using &

Microsoft Excel splits datasets to focus on their exactness. For instance, it will not save the full name of an employee in one cell. It will store the name as first name in one cell and last name in the other.

Combine two similar columns by using the & formula in Excel.

Here is the formula: =A2& &B2

Pro tip: Remember to add the space between and to add the space between name and surname.

14. Advanced data search with the wildcard characters

When searching for a particular item(s) within large datasets, opting for Ctrl+F is not the best option. Thats because you dont always remember the specific item name but may remember only parts of it. Manual search is also not an option, as we discuss 500+ datasets here.

In such situations, opt for the wildcard Excel characters like Question mark(?), Asterik(*), and Tilda(~).

Question mark: It takes the place of a single character
Asterisk: It takes the place of any number of characters
Tilda: It indicates that a character must be used as a normal character, not as a wildcard character

Productivity boosters

If you are a productivity addict, Excel has much in store for you. Make sure to use these simple Excel tips to level up your productivity.

15. Add drop-down menus to track task status and add assignees

Are you using Microsoft Excel as your in-house project management tool? Then, you need to learn how to add drop-down menus to your Excel sheets.

A simple drop-down list will help you to:

Add assignees for each task
Update timelines and sprints for the tasks
Add tags, labels, and categories to the tasks
Add a status bar to each task for regular tracking

To add drop-down menus:

Select the specific blank cells/columns/more than one row
Click on the Data menu in the top navigation
Select Validation, and the Data Validation window will open
Now click on Lists followed by Drop-down List
Now check the In-Cell dropdown button and click on OK

Creating a drop down list in Excel via Ablebits

The texts will change based on the purpose of your drop-down menu. For example, if you add a drop-down menu to add assignees for each task, the text content will be the team members names.

When you have many datasets on your worksheet, manually scrolling from row 1 to row 1000 is not easy. To simplify your job, let us introduce you to an Excel formula that helps you reach your destination data without scrolling.

To reach from the first row to the last row of the entire sheetPress Ctrl and the up arrow () and vice versa
To reach from the leftmost column to the rightmost column of the spreadsheetPress Ctrl and the () and vice versa

17. Organize datasets with colors

Productivity enthusiasts love colors, and what they love even more is organizing datasets with the help of different colors. Spreadsheets let you group your datasets of various sizes with relevant colors.

One of the Excel tips for color-coding your datasets is conditional formatting.

Select the datasets in multiple cells
Go to the top data tab and click on Styles
Select the drop-down menu that says Conditional Formatting. In this stage, select the color coding format from the displayed options
Select OK

18. Add checkboxes within data cells

Productivity enthusiasts love creating to-do lists. Striking off the tasks we have completed is the best thing to do after a hectic workday.

And here is an underrated Excel hack to convert your spreadsheet into a to-do list tracker by adding checkboxes.

Go to the Developer data Tab in the Home tab, and click on Controls> Insert> Form Controls> Checkbox
Click on one cell where you want to place the checkbox
Now drag the cursor down to the cell where the last checkbox will be placed

Pro tip: Cant find the Developer data tab? Do this:

Right-click anywhere on the ribbon and click Customize the Ribbon
Under Customize the Ribbon, check the Developer box and click OK

Optimizing spreadsheets

Make the most of your Excel spreadsheets with the following Excel tips:

19. Remove inactive add-ins

Microsoft Excel Add-ins are often unused and end up slowing down your spreadsheets. It is a good idea to disable unused add-ins to optimize the performance of your entire sheet.

Go to File, select Options, and scroll down to click on Add-ins
Click Manage, followed by Excel Add-ins, and click on Go
Now Untick all the unnecessary add-ins and select OK

20. Avoid using forward and backward referencing

Plan your Excel formulas so they dont refer too much to backward and forward referencing. Frequent forward and backward referencing will result in deferred calculations in situations with complicated calculations and too many formulas.

21. Avoid adding links abruptly within the spreadsheets

Links between your Excel spreadsheet or selected cells are often broken.

With multiple workbooks and a large dataset, they are challenging to find and fix. Minimize the number of links and practice opening all links before starting your Excel operations to stay on track.

22. Keep your table structured

Instead of pasting an unstructured table that doesnt represent the dependent and independent variables, keep the tables as structured as possible. This will result in fewer performance disadvantages and more accurate outputs.

23. Avoid using the VLOOKUP function and use these functions instead

VLOOKUP might be easy, but INDEX and MATCH functions do the same calculations with greater flexibility. Once you use the MATCH function, it stores the results in a blank cell and lets you reuse it multiple times in INDEX statements.

However, this feature is not available with VLOOKUP.

24. Avoid unstable formulas

Unstable formulas like NOW and OFFSET recalculate the entire calculation for the slightest change in the Excel sheet.

If you try to pull data from newer sources, these formulas tend to crash the overall formula and might display an error message. This will help you keep your data security intact.

25. Replace unused formulas with static values

Sometimes, we keep unused formulas within a spreadsheet. These formulas slow down the Excel application and result in delayed outputs. Consider auditing your entire sheet occasionally and replacing unused formulas with static values.

Limitations of Using Excel

Now that you know Excels advanced shortcuts, formulas, and functions, let me tell you a bitter truth you have probably realized alreadyworking on Excel is not always the best feeling.

Excel has a lot of limitations, and here are a few:

Excel doesnt allow you multiple views such as Kanban board view, Gantt chart view, calendar view, or list view. You are stuck with the one view row-column view. Excel lacks efficiency from that point of view
Creating dashboards in Excel means creating charts and graphs manually for different types of selected data. Even though you templatize a few dashboards, there is no way you can use them multiple times as the spreadsheet templates lack simplistic features like widgets, compelling colors, and drag-and-drop builders to pull data from different sources
Excel doesnt have built-in workflows that users need for customization purposes. You must manually add a drop-down list every time, whether you want to add assignees, due dates, or statuses

Meet ClickUp: The Best Excel Alternative

If Excel is not working for you, it is time to select an Excel alternative, and we have the best recommendation for youClickUp.

Here are some features ClickUp users love:

ClickUp Table view lets you create next-level spreadsheets with top-notch data visualization features and helps you build a no-code database with minimum manual effort

Organize, sort, and filter tasks in the ClickUp 3.0 Table View to gain insights faster across all your work

Pull data from relevant sources and create spreadsheets with ClickUp with 15 distinct views, including a Gantt chart, Kanban board, calendar, and list views
The Spreadsheet Template by CickUp is a beginner-friendly spreadsheet to collect critical information about your business, such as customer demographics and more data. You can view the data set in List, Board, Map, and Grid view

ClickUp Project Management Spreadsheet Template

ClickUp has pre-built productivity templates to help you get started with the task at hand instead of wasting time building the template from scratch
ClickUps Editable Spreadsheet Template is a perfect tool to take control of your data and simplify everything from financial forecasting to project planning and budget tracking