Top 10 Windows Excel Tips and Shortcuts To Become A Spreadsheet Pro
Microsoft Excel has become a mainstay for office workers worldwide since launching in 1985. This potent, multi-purpose app is the go-to spreadsheet-maker for several white-collar professions, from project managers to admin assistants and number-crunching finance wizards.  

Yet despite its widespread proliferation, many professionals lack the know-how to perform crucial, time-saving functions. We’ve compiled a nifty shortlist of 10 excellent Excel tips and shortcuts for Windows so you can close the knowledge gap and bolster productivity.

How to remove duplicates in Excel

Big datasets often entail redundant duplicate entries, such as identical mailing addresses for every employee in an organisation. Identifying and removing duplicates can hide unnecessary data and make your spreadsheet easier on the eye.

If you just what a snapshot of duplicate content:

  1. Press Shift + left click to select the desired cells in a column or row (or CTRL + left click to select cells in numerous columns/rows).
  2. With the cells selected, click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. Choose your preferred formatting in the Values with box, then click OK.

To remove duplicate content:

  1. Select all the cells you want to remove duplicate data from.
  2. Click the Data menu and the Remove Duplicates button.
  3. Under Columns, either check or uncheck the columns to remove their duplicates
  4. Click OK.
This process will remove duplicates permanently. Consider creating a backup.

How to filter in Excel

Large datasets often display unnecessary information, making it tricky to find what you need. Filtering out unhelpful info makes it easier to focus on the task at hand.

  1. Select the cell(s) you wish to filter.
  2. Click Data > Filter.
  3. Click the downward Column header arrow.
  4. From the drop-down menu, select the appropriate criteria to filter your data. Options include only displaying data greater than a certain value or values containing specific text. You can also organise data in numerical or alphabetical order.
  5. Click OK.

How to lock cells in Excel

Data accuracy is crucial to every business. Locking cells is an effective way to protect your information from unintentional alterations or malicious sabotage. The more complex a spreadsheet, the more havoc unwanted changes can cause.

  1. Select the cell range you want to lock.
  2. Click on the Home tab, then click the small arrow in the Alignment group to open the Format Cells window.
  3. Click the Protection tab, click the Locked checkbox, and click OK.
On an unprotected worksheet, Locked will be selected by default. The locking function will only come into effect once you protect the worksheet.

  1. Click the Review tab, and select Protect Sheet or Protect Workbook in the Changes group.
  2. Type a password into the Password box for extra security if desired.

The cells are now protected and can’t be changed until you unprotect them.

How to merge cells in Excel

Merging cells removes redundant information to enhance readability, simplify formatting, and improve aesthetics. When merging multiple cells, only the contents of the upper-left cell will remain - the rest will be automatically deleted.

  1. Select the cells you wish to merge.
  2. Click Home > Merge & Center.

To unmerge your cells:

  1. Click the Down arrow on Merge & Center.
  2. Click Unmerge Cells.

How to split cells in Excel

Excel cells often include multiple data. Splitting them up can improve organisation and aid analysis. For example, you may want to split first and second names into different columns or standardise dates by separating the year, month, and date.

Splitting cells replaces the original cell with the first split cell. Consider creating a backup beforehand.

  1. Select the cells you want to split.
  2. Click the Data tab.
  3. Find the Data Tools group and click the Text to Columns button.
  4. Choose Delimited when data is separated by commas or Fixed Width to align data with spaces.
  5. Click Next.
  6. Select the appropriate Delimiter or a suitable Fixed Width (instructions will appear to guide you).
  7. Choose an appropriate Data column format and Destination.
  8. Click Finish.

How to enable macros in Excel

A macro is a powerful tool that executes commands and automates repetitive tasks. Advanced Excel users can use macros to bolster productivity, perform complex calculations, generate in-depth reports, and integrate their work with other programs.

But tread carefully, as malicious macros can pose a significant security risk. As a result, permission to enable macros may be locked to a system administrator.

  1. Click File > Options > Trust Center > Trust Center Settings > Macro Settings.
  2. Choose the most suitable option from the Macro Settings list:
  • Disable all macros without notification.
  • Disable all macros with notification.
  • Disable all macros except digitally signed macros.
  • Enable all macros (can leave you vulnerable to dangerous code).
See Microsoft Support for more information on each setting.

How to create a drop-down list in Excel

A drop-down list limits the entries a user can make in a cell. This handy functionality can speed up the data entry process, enhance accuracy, and standardise input to improve analysis.

  1. Select the cell(s) where you want to create a drop-down list.
  2. Click on the Data tab and click Data Validation.
  3. Set the Allow box to List.
  4. Click on the Source box and enter the data (text or number) for your drop-down list.
  5. Click OK.

Pro tip: separate the data with commas to create a delimited list.

How to freeze rows and columns in Excel

With large spreadsheets, it’s easy to lose track of important information as you scroll through a sea of numbers and text. Freezing rows or columns locks essential info in place, making it easier to contextualise data.

Freezing the top row or first column keeps headings visible no matter how far you scroll.

  1. Click the View tab.
  2. Click the Freeze Panes button in the Window group.
  3. Click the Freeze First Column or Freeze First Row.

To freeze the first two columns or rows:

  1. Click the third column or row.
  2. Click View > Freeze Panes > Freeze Panes.

To freeze numerous columns and rows:

  1. Click the cell to the right of the columns and below the rows you want to freeze.
  2. Click View > Freeze Panes > Freeze Panes.

To unfreeze panes:

  1. Click View > Freeze Panes > Unfreeze Panes.

How to create PivotTables in Excel

The PivotTable is a powerful Excel tool that lets you rapidly analyse data to make comparisons and identify patterns or trends.

For best results, use clean tabular data organised into columns rather than rows. See Microsoft Support for more handy formatting tips.

  1. Select the cells you will use to create the PivotTable.
  2. Click the Insert tab and PivotTable box.
  3. Choose where you’d like to display your PivotTable, either in a New Worksheet or a specific cell of an Existing Worksheet.

How to use auto-fill in Excel

Auto-fill is a handy, time-saving function that lets you rapidly populate adjacent cells with various data types. The easiest way to employ this trick is to use the fill-handle technique.

  1. Select the cell that will be your source.
  2.  Locate the Fill handle (a small black square) in the bottom-right corner of the cell.  
  3. Drag the Fill-handle across or down to populate adjacent columns and/or rows.

Three excellent HP laptops for Excel pros

If sifting through cells comprises a substantial portion of your workday, it’s worth investing in an Excel-ready laptop. Consider the following three options from HP.

HP ProBook 440 14-inch G9 Notebook

HP ProBook 440 14-inch G9 Notebook
Ideal for everyday business use, this potent work laptop packs plenty of power for a reasonable mid-range price.

This 14-inch version of the HP ProBook 440 G9 comes with a 12th Generation Intel® Core™ i7 processor, 16GB RAM, and a roomy 512GB SSD. The crisp full high-definition (FHD) display lets you sort through complex spreadsheets with crystal-clear clarity.

HP Elite Dragonfly 13.5 inch G3 Notebook

HP Elite Dragonfly 13.5 inch G3 Notebook
Starting at a feather-light 0.99 kg, the HP Elite Dragonfly G3 is an uber-portable ultra-book designed for professionals on the move. Despite its lightweight frame, the premium business-ready device boasts top-tier hardware to handle an array of demanding apps - it’s suitable for so much more than Excel.

Under the hood, you’ll find a 12th Generation i7, 16GB RAM, a 512SDD, and a potent Intel Iris® Xe GPU.

HP 250 15.6 inch G9 Notebook

HP 250 15.6 inch G9 Notebook
Many seasoned Excel pros are happy to carry extra weight for the luxury of a larger screen. Boasting a 15-inch FHD display, this HP 250 G9 Notebook makes trawling through compact spreadsheets a breeze.

High-end hardware - an Intel i7 processor, 16GB RAM, and Intel UHD Graphics - gives you more than enough oomph to run Excel. And with an enormous 1TB SSD, there’s plenty of space to store enterprise-sized datasets.

Summary

Microsoft Excel comes with a dizzying array of productivity-enhancing functions, from PivotTables to macros and merging cells.

Upon mastering its top time-saving features, you can fortify your position as the office go-to Excel guru. Take a moment to study our Excel expert tips to enhance productivity and bolster your employment prospects.

Should you need a shiny new laptop to power your extensive spreadsheet endeavours, check out what’s available in the HP Hong Kong Store.


About the author: Harry Stewart is a Tech Takes contributor covering everything from laptop reviews to how-to guides.