Leaders in Innovative Research Solutions

Blog

How to get charts in Excel from MRDCL, Reflect and QPSMR tables

We are often asked what is the best way to get figures from tables created in MRDCL, QPSMR or Reflect 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 Reflect or 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 Reflect and QPSMR users only): Save the CSV file

If you have used Quick Tables in Reflect or 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, Reflect 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, Reflect 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.

Phil Hearn
Author: Phil Hearn - Date posted: 14 February 2017
Category: data-analysis - Tags: excel, charts, powerpoint

« How much do cross tabulations cost? - PowerPoint Presentations vs. Online Dashboards – Which is Better? »