Excel, in 1990, incorporated the pivot tables for version 3.0, as another menu of its application. This made data analysis easier and faster. However, through the use of lookup and reference formulas, conditional sums and combined fields, it is possible to make your own pivot table, manually, even if it is a bit more limited.
A client who uses a SQL database, and exports some data from its delegations to Excel, uses the pivot tables, for data analysis. This client asked us to change the way of presenting the data, at his convenience, and that by means of a variable, changes in the data to be analyzed would be produced. To do this, we created a kind of manual pivot table, using lookup and reference formulas, and we added a combined field, so that the user could change a variable. This approach is explained in more detail below.
This is data for the accounting analysis of expenditure at the client’s offices. Its interest was that the concepts used in the analysis, were presented in a certain order, and that through a combined field, the month could be changed, so that the table, showed the expenses of a delegation, in a concrete month and the accumulated expenses until that month, from the beginning of the year.
Indeed, pivot tables can group data by delegations and months, but the concepts are ordered alphabetically. In addition, the customer requested that the cumulative one be calculated. Anyway, for this case, we use the dynamic tables to prepare the final calculation, grouping the concepts and values on a monthly basis.
Initially, a list of concepts is available, by delegation, month, accounting account and amount. The list is very long and has information on various delegations and months, repeating the concepts, but with different figur.
Below is a dynamic table that groups by delegation and month, the concepts of the previous sheet.
In another book, we make the corresponding calculation on different sheets (one for each delegation), distributing the accounts, according to the order that the client needs. We create a combined field, to select the month of the calculation and present the corresponding figures. Then, to calculate the accumulated amount, we develop a table on the right, with the figures month by month, but accumulating in each month, the figure of the previous month, for each account. In this way, in the column “Accumulated”, we can present the corresponding value, according to the selected month.
Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Spanish language. Note: The data inserted in the images are fictitious.
