Excel Pivot Tables, Search and Logic Functions, Macros
Classified in Computers
Written at on 
English with a size of 16.44 KB.
Explanation
10.1 Pivot tables
What is a pivot table? It is one of the most powerful tools in Excel. They are also called “dynamic” because the data on them can be changed by rearranging or changing the position of the column and row headers in an easy and quick way.
The use of a dynamic table is to obtain information summaries from our data grid in order to be able to quickly analyze big volumes of information. These type of tables can be used in situations such as:
- When you have a table with rows that have different date and you need to organize the information by time periods (month, semester, etc.)
- When you need to change the arrangement in the rows or column cells to show different perspectives from the same data set.
Follow these steps to create a pivot table in Excel:
- Follow the path Insert > Tables > Pivot table
- A dialog box will be opened. This box will allow you to enter the data origin and the destination cells:
- Select the origin data range. This will feed your pivot table.
- Select the destination cells. You can select only the first cell of your pivot table.
- Click OK.
You should see that a blank pivot table is created and the Pivot Table List panel is shown with the fields available for selection ready to be dragged and dropped into the rows of column labels.
You have two options to add the data into the actual pivot table:
- Drag the field names into the Pivot table area on the worksheet.
- Drag the field names into the Pivot table panel areas at the bottom. These areas are linked to the actual worksheet area. As you add the fields into the areas, the table will start populating.
A pivot table can be sorted and filtered once created. Even more, it can be changed just by moving the data source into another field or column. A pivot table can be also formatted with styles to add a personalized look.
You can also apply filters in a pivot table. To do this, click on the arrow next to Column Labels, to display the list where you can find the Label Filters or Value Filters options. Choose one according to your information needs.
To obtain averages on a pivot table, position the cursor in the Total Sums that Excel generates, so you can modify it. It is important to place the cursor right at the cell you want to modify and then right click to display the options menu. Select the Value Field Settings option and once you see a new dialog box, select the function you want to calculate, Sum, Count Average, or other. In this case select the Average option and click OK. It can also be set via the Field Settings button.
You can also create a chart from your Pivot Table, position in the Options/Analyze tab and then click on the Pivot Chart button. The Insert Chart dialog box will appear; select the features of your graph. When done, click on the OK button.
To delete a Pivot Table select the Options tab and then in the Actions group display the menu, select Delete and then click Clear All.
10.2 Search and Logic Functions
The search and logic functions allow you to perform certain operations with the data contained in a spreadsheet.
The search functions help you to find text or numeric values entered in an Excel spreadsheet according to user requirements and criteria used in the syntax. Below you will see them with some examples:
Function | Description | Example |
LOOKUP | Searches for a value within a row or column. | LOOKUP(lookup_value, lookup_vector, [result_vector]) |
VLOOKUP | Finds a value in a column and returns the value in the same position in a second column. Always look at the first column in the specified range. | VLOOKUP(lookup_value, table_array,col_index,num, [range_lookup]) |
HLOOKUP | Finds a value in a row and returns the value in the same position in a second row. Always look on the front row of the specified range. | HLOOKUP(lookup_value,table_array,row_index_num[range_lookup]) |
MATCH | Searches for a value in a list and returns the relative position of the value within the range. | MATCH(lookup_value,lookup_array,[match_type] |
COUNT | Counts the number of cells that contain numbers in a given range. | COUNT(value1, [value2], …) |
The logic functions are used in the decision-making, because according to the result of the function we will have to make a decision.
Function | Description | Example |
IF | Evaluates a condition and returns one value if the condition is true, and another value if the condition is false | IF(logical_test,[value_if_true].[value_if_false] |
AND | Returns the value TRUE if all parameters are true, or returns FALSE if either parameter is false. | AND(logical1, [logical2], …) |
OR | Returns the value TRUE if either parameter is true, or returns FALSE if all parameters are false. | OR(logical1, [logical2], …) |
NOT | Reverses the logical value of the specified argument | NOT(logical) |
10.3 Creating and Editing Macros
A macro is a set of repetitive instructions or actions that are saved to be available whenever they are needed to be executed. Macros help us out to automatize tasks that are done repetitively.
The easiest way to create a macro is by using the macro recorder tool. In order to be able to create macros, make sure that the Developer tab is available on the ribbon as this is where the macro commands are. If you don’t see it in your ribbon, perform the following steps to make it visible:
- Click on the File tab.
- Click on the Options item.
- Click on Customize Ribbon and select the Developer checkbox on the list at the right:
- You should be able to see the Developer tab now:
Recording a macro.
With this option, each of the commands performed by you will be recorded and executed again to repeat them as needed.
|
Modifying a macro.
If there are existing macros that almost fit what you are trying to accomplish you can always edit them. In order to modify macros:
|