How to Become a Pro in Microsoft Excel in 2021?

Microsoft Excel

Microsoft Excel, irrespective of what business or organization you work for, is considered one of the vital skills. Whether it is forecasting, analysing, accounting, or decision-making, Excel is involved everywhere. So, if you are considering joining Microsoft Excel Training and giving excel skills on your resume a boost, this post will help you. Below we are listing some of the most sought-after Excel skills; possessing the practical knowledge of which will make you a pro in the spreadsheet processing software.

Key Skills to Hail Yourself as Microsoft Excel Pro

1) Pivot Tables

It is very tedious to analyse a large amount of data. It is here where Pivot Tables help you. Pivot Tables summarize a big chunk of data making it easier for you to identify data patterns or irregularities. All those who have to work on data analysis using Microsoft Excel, Pivot Tables are a must for you. Not only they help you save a significant amount of time, the remaining time you can utilize for enhancing your productivity at the workplace further.

2) Filters

Filters, as you know, help you to see that data only in which you are interested and the remaining data is simply hidden from your view. Filters have become much more advanced in current as well as upcoming versions of Microsoft Excel. In the latest versions of Excel, you can apply a filter on the basis of cell color as well as number-based values like greater than, equal to, and more. Hence, knowledge of the effective use of filters is a must for anybody who is looking to become a pro in Microsoft Excel.

3) Conditional Formatting

Using Conditional Formatting, you can highlight certain cells on your spreadsheet for which a certain condition is met. For those cells, the cell color will change. Some of the conditional formatting values that you can use in Microsoft Excel are Cell Rules like Top/Bottom, Duplicates, Between, Equal to, Text that Contains, and more. Excel provides you with the facility of creating your own rule as well. As an example, if you wish to find the top 10% of employees in accordance with their sales, you can easily apply the Top 10% conditional formatting rule on your data and get the job done.

4) COUNTIF Function

Out of all the functions present in excel, COUNTIF is probably one of the simplest. However, even though it is simple, its usage comes across multiple times during analysis of the spreadsheet. The function’s purpose is to count the number of cells that are in accordance with the specified requirement. For example, you can use it to check how many times a particular text is appearing in a column, the number of cells that contain a value greater than the one specified in the COUNTIF function condition, and more. For your reference, we have provided below an example as well.

=COUNTIF (B2:B5, “>90”) -> Simply put, this is calculating the number of cells within the range of B2:B5 which are carrying a value greater than 90.

5) Charts or Graphs

Of course, nobody likes to view a spreadsheet that contains just data and a person has to go through a lot of pain to understand what exactly the spreadsheet is trying to convey. Now Pivot Tables does help to solve this issue by summarizing a large set of data. However, wherever necessary visuals must be present, one must utilize charts/graphs on the spreadsheet explaining your analysis of the data. Microsoft Excel holds a considerable number of chart types like Bar, Pie, Line, Column, and more. You can combine Conditional Formatting with Charts/Graphs too. 

6) Programming a Macro

If you regularly have to perform a large manual task on Excel, then knowledge of programming macros is a must for you. This is especially true for people working in accounts and finance or marketing. By programming a macro for the repetitive excel task, you can simply finish off that task in the click of a button. In simple words, macros help you to automate repetitive tasks in Microsoft Excel.

7) Excel Keyboard Shortcuts

Believe it or not, if you wish to perform Excel-related tasks speedily, you must be thorough with certain Excel keyboard shortcuts. Using the keyboard shortcuts instead of relying on a trackpad or mouse, will help you move around Excel cells and do the needful faster. For your convenience, we have provided a set of keyboard shortcuts below.

Shift + F11 > Creates a new worksheet for your current workbook

Ctrl + Home > Takes you to Cell A1

Ctrl + End > Takes you to the last data containing cell

Ctrl + Up Arrow > To get to the top row of the current data set present in the excel.

Ctrl + Down Arrow > To get to the bottom row of the current data set present in the excel.

Ctrl + Left Arrow > To get to the first column of the current data set present in the excel.

Ctrl + Right Arrow > To get to the last column of the current data set present in the excel.

Similar to the ones mentioned above, there exist many other Excel keyboard shortcuts that will increase your speed as far as working on Microsoft Excel is concerned thereby increasing your overall efficiency and productivity at the workplace.

8) Transposing Rows <-> Columns

It sometimes happens when working in Microsoft Excel that the data present in rows is required as columns or it could be the other way around. Excel, under Paste Special, provides a Transpose facility. Hence, you needn’t get yourself involved in the manual process of converting rows into columns or vice-versa.

Conclusion – Become an Excel Power User

These are some of the major excel skills that will help you become a pro as far as Microsoft Excel usage is concerned. Excel is, undoubtedly, hailed as one of the necessary skills for hiring by a majority of businesses or organizations.

To Top

Pin It on Pinterest

Share This