Automatic Updating of PowerPoint and Excel Documents
All parts of Working With PowerPoint (Video)
|Related Online Training modules
|Updating Embedded PowerPoint Charts in a Tracking Study
|Generally it is best to access online training from within Q by selecting Help > Online Training
This applies to Q5.11 or earlier and using the “legacy” exporting option in Q5.12 and later. For the new default updating options in Q5.12 and later, see: Automatic Updating of PowerPoint Documents - Default (Q5.12+)
If you attempt to export to an Excel or PowerPoint document into which you have previously exported, a dialog box will appear saying You have already exported this project to Presentation Name, which is open. The message will also indicate the number of items that can be updated and that are new. The following options are then provided:
- Update which, if selected, will cause the data in the documents to be updated from the Q project, leaving any formatting unchaged (except where the additional data results in a change in appearance). If you are updating cells in an Excel spreadsheet that does not contain a chart (i.e., when exporting to PowerPoint as Excel WorkSheet) you may need to edit the worksheet and resize it to ensure that all cells are visible.
- Export Again will export the tables as new charts or tables.
Changing the file being exported to
In order for a document to be updated, Q needs to know that you wish to export to that document. This is done via the steps described in Changing the document being exported to.
How the updating works
Every table and chart which appears in the Report tree contains an invisible code. When charts and tables are exported form Q to PowerPoint this invisible code is hidden in the exported objects. When Q exports a table or chart to PowerPoint and Excel it scans the documents to see if the code is contained within the document and, if it is found, the option of updating is provided.
This updating mechanism is relatively robust and can be used to update a document from multiple Q projects and also to update multiple different documents from a single Q project.
Additional technical details and limitations
- File names are ignored when conducting updates (i.e., both the Q project and the Office documents can be renamed without causing any complications).
- Q can only update objects that have been exported from Q. There is no way to create a presentation in PowerPoint or Excel and get Q to update it if the charts and tables were not created by exporting from Q.
- Updating works only with charts and tables in Microsoft Excel and PowerPoint.
- Automatic updating of data requires consistently formatted data files (e.g., consistent variable names and code frames). Q has tools for dealing with ‘normal’ changes to trackers, such as new or removed questions and changes to response list. See Updating Projects with New or Revised Data for more information.
- The update will not work if you change the export format (e.g. from To Excel to To Excel with Chart).
- Q has no mechanisms for conditionally modifying text or any graphical objects other than charts and tables exported from Q.
- Prior to Q4.11, charts that have been grouped will not update.
- Statistical tests will automatically update if they have been automatically created within Q. In general, statistical tests can be shown in tables exported from Q and in charts exported from Q which use Q’s internal chart types (i.e., Q cannot put statistical tests on standard PowerPoint chart types).
- The updating ignores the specific data being exported. That is, if you inadvertently change a table in Q from showing Gender to Age then Q will update the exported objects to also show Age.
Troubleshooting updating problems
The updating process is robust and will not break without a good reason. If the updating does not work:
- Review the technical details above to check that you have not inadvertently caused the problem.
- Restart your computer. This is often useful in resetting PowerPoint, which can sometimes get 'confused'.
- Send the PowerPoint file and the QPack to support, giving an explanation as to which chart will not update that was expected to update.
- All standard formatting of PowerPoint charts will be retained (e.g., resizing, changes to colors, changes to fonts), but manual edits to values and labels will not be retained.
- When exporting scatter charts and bubble charts, Q will automatically add labels to data points. These labels will not be updated.
- Microsoft Graph exports cannot be updated.
- Tables split across multiple slides cannot be updated.
- Excel tables will always keep the original Excel Table Style you chose.
- Any manual changes to a table in PowerPoint are discarded when they are updated. The only exception to this is that if the table has the same number of rows or and columns then resizing and positioning of the table will be retained. Any PowerPoint Table Style applied to the table (i.e. selected in PowerPoint from the Table Styles section under Table Tools > Design) will also be retained.
- Tables are reset when updated (i.e., manual formatting changes will be lost).