How to create a dynamic column (bar) graph in calc/excel

Question

I have a table with 6 columns and need make a graphic dynamic equal this other picture. Basically horizontally I would like to have the months and vertically the total. And in the filters you could choose between the other columns. Just that, but it's been impossible for me

I really don't know what else to do to be able to develop a dynamic graph like the one I made on paper ... I have the file, with the table, but I can't develop the graph at all. Can someone give me a tip on how to make it the same?

Answer 1

Generally, you can create a dynamic chart in LO Calc using a Pivot Table. I'll show below how to do this for a data table with a structure like yours. Please notice that I'm not 100% sure about the resulting chart's layout. I assume it should display bars for the %COM and the %VAL values for each month (defined by COMPET) for a given UNI.

I'm using the following simplified data source containing arbitrary values, but with the same structure as yours:

1. Create a filtering pivot table:

1. Select your entire source table;

2. Menu Data -> Pivot Table -> Insert or Edit...

3. In the "Select Source" Window: "Current Selection" -> OK

4. Define the pivot table layout by dragging the fields from "Available Fields" to the appropriate layout fields:

   a. "UNI" to "Filters";

   b. "COMPET" to "Row Fields";

   c. "%COM" and "%VAL" to "Data Fields";

   d. Open "Options" and select "Add Filter"

   e. Under "Source and Destination", set the desired destination (new blank sheet or anything else).

   The dialogue shot now look like this (arrows added to illustrate the steps above):

   

   The resulting pivot chart should look like this (don't worry about the values. Since there's currently no filter applied yet, the pivot chart simply sums the values from every "UNI" for each month):

   

2. Insert a column chart based on pivot table

Now, simply create a simple columns chart with bars from the pivot table: Select the data area including headers (in my example: A5:C9) and create the chart without any modifications. The resulting chart is shown below.

Notice the filter Combobox in the upper left corner, currently labeled "UNI | - all -" (the filter column and the current filter value). LO Calc inserted it automatically since the Pivot Chart contains a filter on the UNI column. The filter ComboBox works the same way as the AutoFilter. Again, don't worry about the values shown since there's still no filter applied (thus, it shows "- all -" instead of the filtered UNI value):

Now, you can use the filter ComboBox for the UNI value in the chart to restrict the chart to a certain UNI value. Notice that the ComboBox values changes from "UNI | - all -" to "UNI | B", since there's an active filter on "B" values from the UNI column:

3. Add a secondary Y axis

Since the two values (COM and VAL) are quite different, a secondary axis may be useful. To add one, do the following:

1. Make sure you're still in chart editing mode (check if there's a thick gray border around the chart. If it isn't, double-click into the chart area to enter editing mode; alternatively, right-cluíck into the chart and select "Edit" from the context menu).

2. Right-click inside the chart area and select "Insert/delete axes";

3. Under "Secondary axed", tick "Y axis"; click OK. A secondary axis should appear.

4. Right-click into one of the value bars, select "Format data series";

5. Under "Options", "Align data series to", select "secondary Y axis"; under "Settings", select "Show bars side by side"; click OK.

Voilá:

That's all - now you have a sheet with a dynamic chart based on the selected UNI value(s).