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:
| Usage | Shortcut keys |
| Insert a row | PC: CTRL + Shift + (+)MAC: Command + Shift + (+) |
| Delete a row | PC: CTRL + (-)MAC: Command + (-) |
| Calculate the sum of a row/column of cell | PC: ALT + (=)MAC: Command + Shift + (T) |
| If you have hidden rows/columns and only want to select visible cells on a worksheet | PC: CTRL + (,)MAC: Command + Shift + (Z) |
| Insert a new worksheet | PC: Shift + F11MAC: Shift + F11/ Shift + fn + F11 |
| Hide selected row | PC: CRTL+ 9MAC: Command + 9 |
| Unhide selected row | PC: CRLT + Shift + 9MAC: Command + Shift + 9 |
| Insert a hyperlink | PC: 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:
| Usage | Formula |
| 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.
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:
- Best Project Management Tools in the Market
- 7 Test Automation Tools in Demand in 2022
- Elcomsoft Extracts Skype Histories from Microsoft Account
- Ethics of Surveying Your Remote Employees
- Five Easy Hacks to Stay Focused at the Workplace
Header image courtesy of Freepik
