Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office

Overview

Word and PowerPoint natively offer the ability to create charts in the document or presentation. Spotlight for Office makes it possible to use cells with Spotlight formulas and then select those cells as the range to be used in the chart.

Specifying the Range of Cells Using Copy Formulas

PowerPoint and Word offer different methods to specify the data range for a chart. Using Copy Formulas is the most streamlined method to select Spotlight cells as the range.

In Practice

1. Start in SpotlightXL and run a view or report.

2. Select the cells to be used in the chart in Word or PowerPoint.

3. Select Copy Formulas from the More menu.

4. Start Word or PowerPoint and select Paste Formulas from the SpotlightWord or SpotlightPPT menu.

5. Click Refresh to see the data.

6. Insert the chart type of your choice.

   The chart is inserted with a set of preliminary data (dummy data), which is displayed in an Excel-style sheet. Click the X to close the Excel sheet.

   

7. Select the rows and columns containing the data to be used in the chart. Do not select POV cells.

8. Click Copy Formulas.

   

9. Now simply select the chart and click Paste Formulas.

   

   The chart first appears blank.

   

   Click Refresh to see the data.

   

   The chart is connected to the data source in Dynamic Planning and you can click Refresh to update the data regularly.

Specifying the Range of Cells Using the Edit Data Window

Word and PowerPoint provide an Edit Data window that looks like an Excel worksheet where you specify row and column headings and data to be represented in the chart. Using the Edit Data window is one way to specify the range of cells for the chart.

In Practice

1. Start Word or PowerPoint and open the file that contains the Spotlight for Office metadata and data items.

2. Select the group of cells containing the rows and columns of data to be used in the chart, and click Copy Formulas from the Spotlight ribbon. Do not select POV cells.

   

3. Put your cursor in a blank area of the document or slide and insert the type of chart you want. For example, in Word, select Insert, Chart, Bar, then click OK.

   The chart is inserted with a set of preliminary data (dummy data), which is displayed in an Excel-style sheet.

   

4. Click the X to close the Excel sheet.

   

5. Select the chart, then on the SpotlightWord or SpotlightPPT ribbon, click Edit Data in Excel. The Excel-style sheet reappears, this time as a complete workbook with an Excel menu.

   

6. Click the SpotlightXL menu item to see the SpotlightXL ribbon.

   

7. Select all the preliminary data in the sheet and press Delete to remove it. You should now have a blank sheet. Select cell A1.

   

8. Click Select Task and select Excel Report.

9. Click More, then Paste Formulas.

   

   The formulas (metadata and data) that you copied from Word or PowerPoint are then copied into the sheet.

   Note:

   If nothing is pasted, you can switch back to your Word or PowerPoint window, select the cells, and Copy Formulas again. See Step 2.

   Data appears as #REFRESH. There is no need to refresh the formulas at this point.

   Note that the formulas pasted in the image below end at cell F10.

   

10. Click the X to close the Excel sheet. This saves the data in Word or PowerPoint. Now you need to tell Word or PowerPoint that this is the data you want in the chart.

11. Back in Word or PowerPoint, select the chart and then click Select Data from the Spotlight ribbon.

    

    Once again, the Excel-style sheet reappears. In it, you see #NAME? for each metadata and data formula that you pasted in step 9.

    To confirm, we see that #NAME? appears in cells and ends at cell F12.

    Note:

    In a future release, #NAME? will be replaced with the appropriate values.

12. Highlight the cells (A1 to F10) and you see the range dynamically filled in the Select Data Source dialog box. Click OK.

    

13. Click the X to close the Excel sheet.

14. Back in Word or PowerPoint, your chart should still be selected. Click Refresh. The chart is now populated with data and you can fine-tune the layout and presentation of it.

    

    The chart is connected to the data source in Dynamic Planning and you can click Refresh to update the data regularly.

Other Chart Options

• Once you have a chart in place that is connected to the data source in Dynamic Planning, you can copy and paste the whole chart to another page or slide in Word or PowerPoint. Each copy of the chart is a separate instance, so you can change the dimensionality displayed in each chart independently. For example, if you set up the first chart to view all 2018 data, you can make copies of that chart to view individual quarters of 2018.

• To make changes to the data you are seeing in the chart, use the Edit Data in Excel button to edit the rows and columns of data. For example, in the chart above, account 6000 is the Total Operating Expense, and account 6400 is Taxes (Expenses). I can delete those rows from the Edit Data in Excel window so that I can see only the categories of expense with relevant data in the chart.

The chart looks better without those accounts.