We are often asked what is the best way to get figures from tables created in MRDCL or QPSMR into Excel charts. The solution is remarkably simple as long as you follow five steps.
Step 1: Create the table which contains the figures you want to chart
If you are using MRDCL, you will need to add the run control parameter CSV2 to the control stage of your MRDCL run. If you want rounded percentages, use CSV2R. This will give you the table you want to use for charting in a CSV file. If you want to chart percentages rather than figures (this is normally the case), you will need to turn off figures by using format NPRC. If you are using QPSMR, you do not need to make the equivalent CSV2 setting as it is set for you by default – in other words, don’t turn the option off! You will usually need to set your table formats to include NPRC to remove figures.
Step 2 (for QPSMR users only): Save the CSV file
If you have used Quick Tables in QPSMR, you will need to preserve the CSV file generated by selecting File > Preserve View File and Exit. If you don’t, the CSV will disappear if you close the table in view.
Step 3: Convert the file to a XLSX format
From here, it’s the same process regardless of whether you use MRDCL or QPSMR. Open the Tables CSV file that is generated in the same folder as the rest of your project and save it as a XLSX file. You can produce a chart in a CSV file, but it will disappear if you try to save it.
Step 4: Open the file and filter the rows
You should have a file like the one below.
Now. comes the clever bit. Choose Data > Filter. Then from the filter on column A, deselect all the options and only check VT, CL, PV as shown below.
Step 5: Select the cells you want
Having clicked OK, you are now ready to select just the cells you need and insert the chart you want. You may want to switch the rows and columns depending on the chart you are producing. You should get an Excel worksheet like the one below.
And, that’s it. Your figures from MRDCL or QPSMR are now in an Excel chart. You can also prepare charts in PowerPoint in the same way.
If you need any further help, please use the You Ask, We Answer form on our home page.