Start Excel Advanced Excel Tips for the Job Market

Advanced Excel Tips for the Job Market

0
15891

In this article we will talk about:

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!

Basic excel and intermediate online courses

1. Deadline for date control

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:

- Text after advertising -

Advanced Excel Tips for the Job Market

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);

Note:

1- In the first cell that will receive the status of the accounts we will enter the following function:

Advanced Excel Tips for the Job Market

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.

Advanced Excel Tips for the Job Market

Using the auto-fill handle, you can easily apply the function to the other cells.

Advanced Excel Tips for the Job Market

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.

2. Other advanced functions

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:

Indirect function in excel

and that if we change the selection to the 2 class, only the names referring to the 2 class will appear:

Indirect function in excel 2

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.

3. Conditional Formatting

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.

Advanced Excel Tips for the Job Market

In the steps below, just select the cells that will receive the formatting and access:

Home> Conditional Formatting> Highlight Cell Rules> Is less than ...

Advanced Excel Tips for the Job Market

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:

Advanced Excel Tips for the Job Market

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.

4. Dynamic Tables and Macros

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.

Dynamic Tables

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:

PivotTable Report - PivotTable

macros

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:

5. Graphics

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:

Advanced Excel Tips for the Job Market

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.

Advanced Excel Tips for the Job Market

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:

How to be an Excel Master for the Job Market

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.

Basic excel and intermediate online courses

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.

- Text after advertising -

NO COMMENTS

LEAVE AN ANSWER

Please, write your comment
Please enter your name