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

What Is the Sunk-Cost Fallacy and How to Avoid It

What Is the Sunk-Cost Fallacy and How to Avoid It

Sunk cost fallacy refers to a situation where an irrecoverable expense (“sunk cost”) has been made and is used as a justification to continue that endeavor, no matter how futile it may be. Almost all of us have made irrecoverable expenses in our day-to-day lives, like buying tickets to a film or a concert.

How News Affects the Stock Market

How News Affects the Stock Market

In January this year, the U.S.-based Hindenburg Research released a report accusing the Indian conglomerate Adani Group of stock manipulation and accounting fraud. The report received widespread media coverage, causing Adani’s stock prices to plummet. The founder and chairman of the Adani Group, Gautam Adani, lost US$34 million of his net worth in just a week after the report was released.

Indian Inventions You Probably Never Knew About

Indian Inventions You Probably Never Knew About

As home to one of the oldest civilizations in the world, India has contributed tremendously to the technological development of the world. Some of the most important inventions that originated in ancient India are the concept of the number “zero”, the game of chess and even the first known accounts of plastic surgery.

The Top 5 Biggest Flops of Shockvertising

The Top 5 Biggest Flops of Shockvertising

Shockvertising (shock+ advertising) is a tactic where an advertiser uses taboo subjects or provocative themes to incite a strong public reaction. This tactic has been known to be quite successful in raising awareness and encouraging behavioral change surrounding acquired immunodeficiency syndrome (AIDS) and human immunodeficiency virus (HIV).

Unleashing the Power of AI: Can It Rival the Divine

Unleashing the Power of AI: Can It Rival the Divine

In January this year, Google engineer Sukuru Sai Vineet created GITA GPT (generative pre-trained transformer). GITA GPT is a GPT-3 based artificial intelligence (AI) chatbot that references the Hindu sacred book Bhagwat Gita to answer questions about people’s issues.

A Look at the Top Nepo Babies in Tech

Born to Succeed: A Look at the Top Nepo Babies in Tech

A buzzword floating around the internet nowadays is “nepo baby” (short for nepotism baby). The term refers to the children of individuals who have succeeded in a specific industry. These children are set up for success right from the get-go, thanks to their parents’ fame and connections.