If you manage a large database that you need to update frequently, then you need to create a pivot table with dynamic range . It will save you a lot of work and headaches. You will observe how the data flows according to your needs.
Index( )
- Create pivot table
- Create the dynamic range
Create pivot table
First it is good that you know how to make or create a pivot table in Excel . It is an excellent and very advanced tool that will help you calculate, summarize, analyze and establish data statistics. The pivot table will allow you to observe and compare trends or patterns between the data.
- Open the Office Excel program.
- In your spreadsheet, you must already have a table with its columns and rows filled with data. For example, your database.
- Select any cell in the table. Activate the “Insert”tab and click on the “pivot table” icon .
- The “Create Pivot Table”window will appear . This window will cover the table collecting the data contained in it.
- You click on “Accept”.
- It will immediately create another spreadsheet.
- In that new sheet, on the right side you will find a window called “List of pivot table fields”. The fields of your source table are contained. You just have to mark the fields by activating the box to add it to the report. And it will be reflected on the sheet.
- At the bottom you will notice several windows: Column Label, Row Label, Values and Report Filter. If you exchange the fields between these windows you will see how the table organizes your data.
When creating the pivot table , if your data is numeric, the table automatically assumes that it has to total as a sum . Therefore, at the end of the process you will see a grand total. Now if you need to update your list, so as not to get out of hand with your perfect order, there is a way. This is where the dynamic range comes in .
Create the dynamic range
Before getting into the matter. A range in Excel refers to the set of grouped cells that we can refer to in our formulas. But when you increase or decrease the data, you must modify the formulas to fit the new range. The solution is the “Dynamic Ranges” . For creating dynamic range, we will employ several functions. The function OFFSET and COUNTA. Below we will show you step by step what to do:
- Simple mode with 1 column of data. Activate the “Formulas”tab .
- Press the “Name Manager” box. Tap the “New …” button.
- In the window “New name”we put the name of the matrix. Preferably a label associated with the data.
- In the “Refers to” field you will write the formula
= OFFSET (A2; 0; 0; COUNTA ($ A: $ A) -1; 1). When it indicates A2 it is indicating from which cell the starting range will cover. Then click on “OK”. - Once the range is created, press the “close” button.
- Select a cell outside the table that reflects the result. And write: = ROWS(name of the range or matrix that we create). For example. = ROWS (FRUITS).
- Then you will see that when you delete or add data, it will give you the updated result. Namely, the total number of rows.
- Complex mode interacting with pivot table. On the sheet with your pivot table already created. Repeat steps 2 and 3.
- In “Refers to” you will write this formula. OFFSET ($ A $ 2 ,,, COUNTA ($ A: $ A), COUNTA ($ 2: $ 2)). Remember that the parentheses must end in black. And click on “OK”. With this formula you mark the entire data range in the table. For more information about the COUNTA function , go to microsoft support for excel.
- Go to Top Menu – Options – Change data source.
- In the window, in the table or range field, write the name of your array that contains the new formula. And accept”.
- In this way, when you add or remove data from the table, on the sheet where you have the pivot table, just by clicking on “Update” you will see the update automatically.
It’s amazing how simple tools like creating a pivot table with dynamic range make your life easier so you can comfortably work with large volumes of data. Even Excel has many functions for working with databases . Leave us your impressions or opinions regarding the subject.
