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

Navigating the Growing Crisis of Space Debris

Orbital Fallout: Navigating the Growing Crisis of Space Debris

Ever since we stepped into the space age back in the 1950s, we’ve been busily sending rockets and satellites up, up and away, painting our mark way beyond our planetary borders. But here’s the thing—our ventures have left the cosmos littered with about 2,000 operational satellites and another 3,000 that are just space junk now.

Pryon Secures US$100 Million in Series B to Advance AI in Knowledge Management

Pryon Secures US$100 Million in Series B to Advance AI in Knowledge Management

Pryon Inc., a North Carolina-based company specializing in integrating artificial intelligence (AI) with knowledge management, has completed a Series B investment round, raising US$100 million. The funding was led by Thomas Tull’s US Innovative Technology Fund (USIT), with contributions from both new and existing investors, including Aperture Venture Capital,

Amazon Launches Upgraded AI Enhanced Fire TV Sticks

Amazon Launches Upgraded AI Enhanced Fire TV Sticks

Amazon has rolled out a series of updates to its Fire TV offerings. The new features include an improved conversational voice search powered by generative AI and Fire TV Ambient Experience advancements. Among the latest hardware releases are the Fire TV Stick 4K Max and the Fire TV Stick 4K, incorporating the enhanced Fire TV Ambient Experience.

Interactive Learning with Augmented Reality

Interactive Learning with Augmented Reality: Applications, Benefits and Challenges

Ever wondered what it would be like if your textbooks could talk, if the illustrations in your lessons could come to life, or if you could step into history rather than just read about it? Welcome to the world of augmented reality (AR) in education! Like a magic wand, AR can turn the abstract into tangible and the mundane into extraordinary, unfolding boundless educational possibilities.

How AI Threatens Your Password Security

Unlocking the Dangers: How AI Threatens Your Password Security

You may have heard of artificial intelligence (AI) technology’s many cool capabilities, such as assisting doctors or predicting the weather. However, there is something not-so-cool we need to discuss: AI could make our passwords less safe, which is concerning.