Microsoft Excel is a powerful tool that can be used to automate tasks, analyze data, and create charts. However, even the most experienced Excel users can learn a few new tricks to save time and effort. Here are 6 Excel hacks that you should know with examples.

1. Use keyboard shortcuts. 

There are hundreds of keyboard shortcuts that can be used in Excel to speed up your workflow.

  • Format cells: Press Ctrl + 1 to quickly access the Format Cells dialog box for formatting options such as number format, font, alignment, and borders.
  • Insert current date or time: Use Ctrl + ; to insert the current date and
    Ctrl + Shift + ; for the current time.

2. Use name ranges.

Named ranges are a way to give cells or ranges of cells a descriptive name. This can make your formulas easier to read and understand. For example, you could name a range of cells “Sales” so that you can easily refer to it in your formulas.

  • Assign names to specific cell ranges: select the range then go to the Formulas tab > Name Manager or use the shortcut Ctrl + F3 to define/edit the names.

Name Ranges

Example of Name Ranges

3. Use conditional formatting. 

Conditional formatting allows you to highlight cells based on their values. This can be used to identify errors, track progress, or highlight important data.

  • Highlight duplicates or unique values: go to the Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values and use the dropdown to highlight duplicate or unique entries within a range.
Highlight duplicate values

Example of highlighting duplicates

  • Color scales and data bars: go to the Home tab > Conditional Formatting > Data Bars or Color Scales to visualize data patterns and variances.

Example of data bars

Example of data bars

4. Use data validation.

Data validation allows you to restrict the values that can be entered into a cell. This can help to prevent errors and ensure that your data is accurate.

  • Restrict values entered: go to the Data tab > Data Validation (Data Tools panel) and set criteria to for example only allow numbers to be entered into a cell.

5. Use pivot tables.

Pivot tables are a powerful tool for analyzing data. They allow you to summarize and rearrange data in a variety of ways.

  • Drill down into details: double-click on a summarized value (Grand Total) within a PivotTable to display the underlying data and analyze it in more detail.
  • Use slicers for filtering: under the PivotTable Analyze tab > Insert Slicer for specific fields to easily analyze and compare subsets of data.

Example of pivot table slicers

Example of pivot table slicers

6. Use functions.

By combining various functions, you can generate highly sophisticated Excel formulas capable of performing advanced tasks at the press of a button. A formula consists of 2 or more functions.

  • VLOOKUP and HLOOKUP: These functions allow you to search for a value in a specific column or row and retrieve a corresponding value from a different column or row.

Example of VLOOKUP function

Example of VLOOKUP function

Example of HLOOKUP

Example of HLOOKUP function

  • IF function: Use the IF function to perform conditional calculations or evaluations based on specified criteria.
  • SUMIFS and COUNTIFS: These functions enable you to calculate sums or counts based on multiple criteria, allowing for more advanced data analysis.

These are just a few of the many Excel hacks that you can use to save time and effort. By learning these hacks, you can become a more efficient Excel user and get more done in less time.

About Zobility

Zobility, an RGBSI brand, focuses on providing innovative talent management solutions within the mobility and high-technology sectors. To achieve talent mobility, we aim to deliver organizations with access to the appropriate technical skills from anywhere in the world by mobilizing career growth for employees.

Work with Zobility

We leverage our established network of top companies around the globe to help you find the right career that aligns with your expertise and goals. Our jobs are in the areas of engineering, mobility, IT, computer systems, and manufacturing.

View our current openings through our job portal.

Follow us on LinkedIn