Microsoft Office is one of the most popular software packages with over 1.2 billion users globally. Excel makes around 10% of this figure thanks to its incredible data visualization, table, and accounting functions.
According to the official report, Excel provides users with almost 500 different operations. Although most companies demand a high level of Excel proficiency, the fact remains that the vast majority of young people cannot fulfill this criterion.
A study reveals that over 60% of students cannot pass the intermediate level Excel exam. But if you really want to be recognized as a serious young professional, you must learn a little bit more than just the basics. In this post, we are going to show you seven secrets in Excel for beginners. Let’s take a look!
1. Add More Than One New Row or Column
Table of Contents
Designing an Excel spreadsheet, you are probably going to need more than just a few rows or columns. Most people create tables with dozens or hundreds of entries, but this can be a painful and time-consuming process if you do it manually.
However, there is a shortcut. You can add multiple columns or rows by highlighting the already existing entries and doing the right click. After that, you will see and choose the “Insert” option and Excel will react by adding the selected number of rows or columns.
2. Speed up Inputting Complicated Terms with AutoCorrect
You probably know that Microsoft Word has the AutoCorrect option? Well, it turns out Excel has the same feature as well, but this one is designed with the goal to help you add complicated entries much quicker.
Let’s say you need to add textual value “Samantha Gilbert” to multiple cells. Writing a name takes time and often results in spelling errors, so you better utilize AutoCorrect to speed up inputting such a complicated term. How come?
Go to File → Options → Proofing → AutoCorrect Options. There, you will see options REPLACE and WITH. In the case of Samantha Gilbert, the easiest solution is to replace SG with her full name.
3. Use Filters to Search
Larger datasets can get pretty awkward if you want to search a specific group of values. In this case, you need to use a tool that allows you to highlight only the targeted entries. This tool is called the Filter. Simply find the Filter icon on top of the column and Excel will show you all the values you have there. The feature allows you to select or deselect all items, but you can also select only one (or a few) item that really interests you.
4. Conditional Formatting
Sometimes you want to change the color of cells containing the targeted information. For instance, content creators at ResumesPlanet and UK Careers Booster use this function to distinguish between articles based on the word count.
To do this, they mark the targeted cell group and go to Conditional Formatting in Home Menu. The pop-up window appears and asks you to explain the formatting principle. At that point, they can choose to highlight posts longer than 1000 words.
5. Eliminate Duplicates
Let’s say you are a sales manager who wants to create a completely credible email subscriber database. This is important because the list is flooded with duplicate entries coming from different sales agents. Fortunately enough, you can perform this task in Excel very simply.
Start by highlighting the row or column you want to rearrange. After that, enter the Data tab → Remove Duplicates. Excel will then show you a pop-up window that asks you to confirm the action. Once you do that, the system will eliminate duplicates.
6. Basic Calculations
As a superior accounting tool, Excel provides users with a plethora of mathematical operations. However, we want to show you how to complete the basic math functions: adding, subtracting, multiplying, and dividing.
The process is the same as regular paperwork mathematics since you use + to add, – to subtract, * to multiply, and / to divide values. So, if you want to multiply cells A4 and B4 and show the result in C4, you just need to enter the following instructions in the C4 cell:
7. COUNTIF Function
If you want to count the exact number of certain values appearing in the spreadsheet, you can use the COUNTIF function. The formula looks like this: =COUNTIF(range, criteria)
- Range represents the area you want to cover with this search. For instance, it could be E:I and cover columns from column E to column I
- Criteria represent the value or textual information you are counting. For instance, it could be “Samantha Gilbert” once again
Therefore, you will enter =COUNTIF(E:I, “Samantha Gilbert”) to the desired cell and it will immediately show you the number of cells containing this name.
Microsoft Excel is a powerful program that can help you automate most of the business intelligence processes. However, you need to understand more than the basics to get the most out of this tool. This post showed you the seven most practical Excel secrets for beginners. These tips won’t make you an Excel guru, but they will definitely set the foundation for future lessons, so do your best to remember our suggestions.
Samantha R. Gilbert is a full-time journalist and a part-time blogger at the online resume writing service. Samantha works at the local publishing agency, but she uses her spare time to write articles about IT, education, and digital marketing.