5 Essential Tricks to Master Microsoft Excel

5 Essential Tricks to Master Microsoft Excel

Upgrade your MS Excel skills with these tricks!

Microsoft Excel (MS Excel) is a must-have for any business. It is a powerful spreadsheet software application that allows users to store, organize and analyze data. While Excel is fairly easy to use, it can be tricky to become an expert. Don’t know where to start? We have compiled a list of easy-to-follow hacks that will help you become a pro at Excel!

1. Use and memorize shortcut keys

MS Excel can be daunting for those unfamiliar with its many features and functions. The key to mastering MS Excel is to learn and use shortcut keys. Shortcuts can help you quickly access the features and functions you need without having to search through menus or toolbar options. In addition, shortcuts can help you automate repetitive tasks, freeing up your time for more important tasks. 

There are many shortcut keys you can use in Excel, and the best way to learn them is to practice regularly. Once you start using them, you’ll work faster and more efficiently. Here are some of the most useful shortcut keys: 

UsageShortcut keys
Insert a rowPC: CTRL + Shift + (+)MAC: Command + Shift + (+)
Delete a rowPC: CTRL + (-)MAC: Command + (-)
Calculate the sum of a row/column of cellPC: ALT +  (=)MAC: Command + Shift + (T)
If you have hidden rows/columns and only want to select visible cells on a worksheetPC: CTRL + (,)MAC: Command + Shift + (Z)
Insert a new worksheetPC: Shift + F11MAC: Shift + F11/ Shift + fn + F11
Hide selected rowPC: CRTL+ 9MAC: Command + 9
Unhide selected rowPC: CRLT + Shift + 9MAC: Command + Shift + 9
Insert a hyperlinkPC: CRLT + KMAC: Command + K 
Edit active cell (which means to continue adding in data without replacing the existing data)PC: F2MAC: fn + F2 

2. Master in Excel formulas

Formulas is the key to unlocking the power of MS Excel. Formulas are equations you can input in the formula bar (the bar next to “fx”) to perform calculations on your data. You can perform complex calculations, manipulate data and automate tasks with the right formula. For example, you can use a formula to calculate the sum of a range of cells or to count the number of cells that contain a certain value. Here are some of the most useful formulas:

UsageFormula
Calculate the sum of values of cells C2 to C4=SUM(C2:C4)
Count the total number of entries between C2 to C4=COUNT(C2:C4)
Calculates the average of the selected cells (e.g. C2, C3 and B4) or range of cells (e.g. C2:C4)=AVERAGE(C2, C3, B4) or =AVERAGE(C2:C4)
Find a remainder after a number/cell (i.e. the dividend) is divided by another number/cell (i.e. divisor)=MOD(9,3)  The cell will display “0” because the remainder of 9/3 is 0.

 Here are some tips to help you get started:

  • Start by familiarizing yourself with the most common MS Excel formulas, especially the ones that you have to use frequently at work. 
  • Take time to practice using these formulas on small data sets. This will help you get comfortable using them and understand how they work.
  • When working with large data sets, break the data down into smaller chunks so that it’s easier to work with. This will avoid mistakes in your calculations.
  • Make use of MS Excel’s built-in error-checking features. These can help you catch mistakes so that you can correct them before they cause problems later on.

3. Enter zero (0) before a number

Though adding zero (0) before any number won’t make any sense, sometimes you might just need to do so. However, our dear Excel will remove any zeroes before a number once you finish editing the cell. 

Enter zero (0) before a number

Here’s a hack to add zeroes before a number: add a single quote (‘) before the number. For example, if you want to input “0009” in a cell, type in “ ‘0009”. 

4. Name a column/row

One of the most useful features of MS Excel is the ability to create named ranges. Named ranges allow you to refer to a specific range of cells by name rather than by cell number. This can be very helpful when working with large sheets, saving you time and hassle when selecting ranges. 

To create a named range, follow the following steps:

Step1: Select the column(s) or row(s) you want to name. 

Step 2: Click “Formulas” on the ribbon menu and then the “Create from Selection” button. A prompt will ask you if you want to name the selected range according to the top row/column or bottom row/column. 

If you want to select the range of cells under a certain name, click the name in the drop-down menu on the left of the formula bar. 

5. Import tables from online resources

What would you do if you came across a table of data that you need from a website? If you are still inputting data cell-by-cell, we have found the perfect tip for you. 

Here’s how you can import a table from a website with just a few clicks:

Step 1: Copy the web address that contains the table you need.

Step 2: Go to Excel and click “Data” on the ribbon menu.

Step 3: Click “From Web” and paste the web address.

Step 4: Select the table you need among the available ones from the website and click “load”.

For MAC, there are a few more extra steps:

Step 1: Copy the web address that contains the table you need.

Step 2: Paste the address in a blank Word document. Choose “Plain Text (.txt)” when saving the file.

Step 3: Locate your file in Finder, right-click the file and click “Get Info”.

Step 4: Click “Name & Extension” and replace “.txt” with “.iqy” after the file name. Click “Use .iqy” on the prompt that pops up.

Step 5: Go to Excel and click “Data” on the ribbon menu.

Step 6: Click “Get External Data” and then “Run Web Query”. Choose the file you saved in .iqy format.

What’s more, when you select the table and click “Refresh” on the menu, the table will be updated automatically.

Becoming an Excel expert is not as hard as it seems. In fact, with the right tools and a little bit of practice, you can be well on your way to becoming an Excel master. We hope these four hacks have given you a good starting point for your journey to Excel mastery. 

Start practicing today!

Also read:

Header image courtesy of Freepik

SHARE THIS STORY

Share on facebook
Share on twitter
Share on linkedin
Share on email

RELATED POSTS

Companies That Are on a Hiring Spree amid Layoffs at Twitter, Meta and Other Big Tech

Companies That Are on a Hiring Spree amid Layoffs at Twitter, Meta and Other Big Tech

The tech industry is facing a slew of staff cutbacks since the beginning of the year. In November 2022, the internet went into a frenzy at Elon Musk laying off most of Twitter’s employees. A few days later, Meta also announced its own round of layoffs, letting go of about 13% of the workforce. Later, Salesforce also confirmed it had dismissed hundreds of workers to cut expenses.

4 Business Sectors Reaping Profit from FIFA World Cup 2022

4 Business Sectors Reaping Sweet Profit from FIFA World Cup Qatar 2022

The 2022 FIFA World Cup 2022 in Qatar will generate roughly US$6.5 billion in revenue, topping the previous record of US$5.4 billion from the 2018 World Cup in Russia. Thanks to the once-in-four-year tournament, Qatar’s GDP is estimated to grow by 4.1% in 2022, and the tournament alone could add up to US$20 billion to Qatar’s economy.

Transparency in the Post FTX World What is Proof of Reserve

Transparency in the Post FTX World: What is Proof of Reserve?

In November this year, two big league crypto businesses, FTX and BlockFi, filed for bankruptcy. FTX had a death spiral after news broke out that the Sam Bankman-Fried-owned exchange had used customer funds to make risky bets through his hedge fund Alameda Research. On the other hand, FTX was closely associated with BlockFi, with them having signed a loan agreement with each other and BlockFi holding US$355 million in digital assets on FTX.

How Do Recommendation Engines Work

How Do Recommendation Engines Work?

Picture this: You just finished a film on Netflix and want to follow it up with something similar. Luckily, Netflix comes to the rescue and gives you the perfect suggestions to continue your weekend movie binge. This isn’t just a hypothetical scenario but something a lot of people actually go through.

Quantum Computing Has a Cybersecurity Problem. Here’s How Experts Are Solving It

Quantum Computing Has a Cybersecurity Problem. Here’s How Experts Are Solving It

In 2019, Google used its quantum computer, the Sycamore machine, to prove that quantum computers can solve a problem in mere minutes. Experts working on the quantum computer found that their system could execute a calculation in 200 seconds, whereas a standard computer would take 10,000 years to complete. What on earth is this powerful tool?

Here Are Some Alternative Sites People Are Jumping To

Musk May Have Killed Twitter: Here Are Some Alternative Sites People Are Jumping To

Ever since Elon Musk purchased the social networking site Twitter for US$40 billion, things haven’t been looking too good for the company’s future. Not only did Musk fire over 50% of the employees soon after stepping on board as the new chief executive officer, but he also intends to allow maximum freedom of speech. This can end up making Twitter a cesspool of racism and misogyny, as well as other forms of hate speech.