In this article we will talk about:
- Deadline for date control
- Other advanced functions
- Conditional Formatting
- Dynamic Tables and Macros
- How to be an Excel Master for the Job Market
It is known that the Excel is one of the most used software in the corporate environment. Even in companies with complex software or ERPs, it turns out that small controls and activities are done using Excel and, thus, knowing this tool has been one of the demands of the job market.
By having useful resources in various tasks, many companies end up requiring more than the basic knowledge of their employees. The time has come when all you know how to do I SOMASE e PROCV were sufficient and, therefore, it becomes fundamental to learn some functions of the advanced Excel. With that in mind, we've separated some tips so you can learn more about Excel and excel at your work. Follow us!
The Deadline feature in Excel is very suitable for those who want to organize processes that involve delivery dates and deadlines. See the example below where we have a table to control the monthly accounts to be paid in a company:
In order to know the status of the accounts, we will use the following functions:
Today() - To return the current date, whenever we open the worksheet;
SE () to compare the current date with the due date of the account and to present the status (Deadline Expired or In Time);
1- In the first cell that will receive the status of the accounts we will enter the following function:
Note that we are telling Excel that if the account's due date is less than the current date (represented by the Today function), then it should return the "Expired Time" message, otherwise the result should be the message "Within of the Term ". For the above example, we take today's date as 14 / 08 / 2015.
2- At the end of the function, just hit enter and check the result.
Using the auto-fill handle, you can easily apply the function to the other cells.
This type of procedure can be done for accounts payable, accounts receivable, dates to contact customers in negotiation processes, deadline to order new inventory and so on.
The term deadline is just one example of applying advanced functions that can help you a lot. Some other excellent applications are below:
I will not go into the details of this function, if you are interested in going deeper I highly recommend the post we wrote on the subject. But just to give an example, you can create lists conditioned to values from other cells. Notice that in the first image a list of only the 1 class appears:
and that if we change the selection to the 2 class, only the names referring to the 2 class will appear:
I used the example of a class and the names of the students, but could be the example of countries and states, areas of the company and employees or several other applications that involve different groups with items inside them.
For you to have an idea, with DESLOC you can create print intervals and dynamic lists. In addition, combining DESLOC with CORRESP gives you a great alternative to PROCV.
Indicated to highlight cells based on a pre-set condition, conditional formatting is ideal for facilitating color-based, shape-based, and cell-based data analysis. For a better understanding, see the example below. In it we have a table with inventory control of an appliance store. In this case, we want to know which products are below 2 units. For this, we use conditional formatting to highlight these items.
In the steps below, just select the cells that will receive the formatting and access:
Home> Conditional Formatting> Highlight Cell Rules> Is less than ...
A new tab will open. Simply enter the 2 value in the "Format cells that are MINORS:" field.
Choose the formatting to apply to cells, and if you prefer, you can create a custom formatting. Click OK and see the result:
Conditional formatting is very useful for making your spreadsheet easier to read. Hence, it is essential for presentations or while showing data for some other person.
Continuing with the advanced learning and Excel tips, these 2 features are quite important to simplify your life. With them, you can save an absurd amount of time that would use to get the same results.
PivotTables are sensational tools to simplify data. So if you have a database or table with lots of information, you need to understand how to use pivot tables to spend less time and get a lot more insights. Here's where to start:
Macro macros are mainly used to avoid rework. Think of them as recorders of boring routines. Basically the macros automate processes (steps in Excel) that you would have to do. A very simple example is to copy and paste an item from a form to a database. If you know how to use the macros, you can record a routine that takes the data and adds it to your table with just one click. See good tips for anyone who wants to find out more about it:
- Learn how to create your first macro in Excel
- Understand how to create and combine macros
- 10 Examples of Macro Usage
The advantage of using graphics to present information is the visual and summary capability they present. Let's look at a simplified example of using charts for a cash flow analysis of a company. Below we have the balance of the company monthly from January to December and we want to see a column chart that shows us this in a more practical way:
To do this, simply select the cells with information that will compose the chart and select the "Insert" option. After that, choose the bar model for better visualization and comparison of the data.
After selecting the desired template, the graphic will be created automatically.
Changes of colors, formats and other models can be made according to the user's needs. See other charting applications that can help you make great reports:
Having advanced knowledge in Excel for the job market has become as important as knowing a new language. To be prepared for better opportunities, it is worth learning the features that this software offers for the various activities in the corporate environment.
If you want to know how to become an Excel master, I recommend you start by reading this article from top to bottom (and also the articles listed). After that, see our Advanced Excel course, in addition to many of the topics covered in this post, it still has several other videos explaining the step by step how to do each of these applications.
If you want to measure your knowledge I recommend this post with 10 basic and advanced Excel exercises.