Sunday, September 22, 2013

Using Calculated Fields in Dynamic Tables with Excel

Honestly, I don't consider myself an expert in dynamic tables, although many people think that actually I am this is just because I know many tricks about Excel, however I don't use dynamic tables a lot and therefore there are things about this tables than I don't know.

One day, when I was at work, a partner called me to watch his computer because he had a big doubt about something so he decided to ask me about it. He was managing a huge dynamic table that have a bigger simple table as data source, this dynamic table resumed all sales from region, but the problem was that he needed to show that resume not with the exact amount but rounded to thousands.

At that moment I couldn't help him because I didn't know how to do it, but now I took a time for testing ways of doing that and I want to show you what could have done at that time.

First at all create a simple table with few regions and sales value in 3 currencies.

With this information, select it and  go to "Insert" tab and select "Dynamic Table" to create one based in the simple table created above.

You will have your dynamic table created, select  "Regiones" field to have the first column of the new table.

 Now select the "Options" tab and inside here select "Fields, Items & Sets" button to select the option "Calculated Field" from the menu.

Once you see the new window the next step is to set the new field, first put a name for it and second, place the function for this, in this case you have to use a previous existing field to divide it over 1000 and this result has to be rounded by using the native function round provided by Excel.

Doing so will give you a new column in your table that shows the amount of sales but in thousands as it is required.

If you want to see the original value just check the original field from the panel, in this case is the "Dólar" field.

Using this method will provide you many useful ways to present information in an easy way, it depends on the complexity of the functions to be used to calculate the new field.

No comments:

Post a Comment