Using Spotlight for Office Summary
- 7 Minutes to read
- Print
- DarkLight
- PDF
Using Spotlight for Office Summary
- 7 Minutes to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
Using Spotlight for Office in PowerPoint
Once you have copied data from a SpotlightXL View or Report into PowerPoint, you are ready to work with the Spotlight menu ribbon.
| Menu Ribbon Item | Purpose |
|---|---|
Refresh, Refresh All Open Reports | Connects to the Dynamic Planning data source and retrieves the data value for each cell on the current slide. Each cell is defined by a member intersection, which you can view with the Design Manager. You can update all data for all slides by selecting Refresh All Open Reports. |
Analyze in Excel | For the current data cell selected, returns you to Excel and displays the member intersection where the data came from. You can then zoom in on any of the dimensions to learn how the data item was derived. |
Design Manager | Opens the Design Manager box and displays the member intersection or dimension information for the current cell selected. If the current cell is a data item, Design Manager displays the model, and all the dimensions and members that describe the data. If the current cell is a dimension or member name, Design Manager displays the model, dimension, and member name. In the Design Manager, you can change all non-POV dimension members associated with the current cell. Click the Member Select icon next to the current value, select a new member, and click Select. Note: You can also double-click a cell to view the metadata associated with it. |
Number Format | Opens the Format Cells box and lets to you select a formatting style for the selected data cells containing numbers, similar to the options in Excel. To change the format, select the category of number format you want, specify options, and click OK. |
Copy Formulas | Copies the metadata and data formulas associated with the selected cell(s) to the Clipboard. These formulas can then be pasted into another slide in PPT, into SpotlightXL, or into SpotlightWord. |
Paste Formulas | Pastes the metadata and data formulas from the Clipboard to the current slide in SpotlightPPT. |
Selection | Opens the Selection pane. In this pane, you can Show All Spotlight cells, Hide All Spotlight cells, and see a list of all Spotlight cells on the current slide. If you select an item in the Selection list, that cell is highlighted on the slide. You can select multiple items on the list with Ctrl-Click. If you want to delete those cells from the slide, press the Delete key. |
Select Data | Specifies the range of data items to be used in a chart. |
Edit Data in Excel | Allows you to use the Excel interface to paste in the cells from Spotlight that will be used in a chart. |
Using Spotlight for Office in Word
| Menu Ribbon Item | Purpose |
|---|---|
Refresh, Refresh All Open Reports | Connects to the Dynamic Planning data source and retrieves the data value for each cell on the current page. Each cell is defined by a member intersection, which you can view with the Design Manager. You can update all data for all page and open documents by selecting Refresh All Open Reports. |
Analyze in Excel | For the current data cell selected, returns you to Excel and displays the member intersection where the data came from. You can then zoom in on any of the dimensions to learn how the data item was derived. |
Design Manager | Opens the Design Manager box and displays the member intersection or dimension information for the current cell selected. If the current cell is a data item, Design Manager displays the model, and all the dimensions and members that describe the data. If the current cell is a dimension or member name, Design Manager displays the model, dimension, and member name. In the Design Manager, you can change all non-POV dimension members associated with the current cell. Click the Member Select icon next to the current value, select a new member, and click Select. Note: You can also double-click a cell to view the metadata associated with it. |
Number Format | Opens the Format Cells box and lets to you select a formatting style for the selected data cells containing numbers, similar to the options in Excel. To change the format, select the category of number format you want, specify options, and click OK. |
Copy Formulas | Copies the metadata and data formulas associated with the selected cell(s) to the Clipboard. These formulas can then be pasted into another page or document in Word, into SpotlightXL, or into SpotlightPPT. |
Paste Formulas | Pastes the metadata and data formulas from the Clipboard to the current page in SpotlightWord. |
Select Data | Specifies the range of data items to be used in a chart. |
Edit Data in Excel | Allows you to use the Excel interface to paste in the cells from Spotlight that will be used in a chart. |
Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office
Word and PowerPoint natively offer the ability to create charts in the document or slide deck. They provide an Edit Data window that looks like an Excel workbook where you specify row and column headings and data to be represented in the chart. Spotlight for Office makes it possible to use Spotlight formulas in the Edit Data window and then select those cells as the range to be used in the chart.
Start Word or PowerPoint and open the file that contains the Spotlight for Office metadata and data items.
Select all the cells and click Copy Formulas from the Spotlight ribbon. Do not select POV cells.
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.
Click the X to close the Excel sheet.
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.
Click the SpotlightXL menu item to see the SpotlightXL ribbon.
Select all the preliminary data in the sheet and press Delete to remove it. You should now have a blank sheet. Select cell A1.
Click Select Task and select Excel-based Report.
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 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 F12.
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.
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.Highlight the cells (A1 to F12) and you see the range dynamically filled in the Select Data Source dialog box. Click OK.
Click the X to close the Excel sheet.
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 7000 is Other Income (Expenses). I can remove those rows so that I can see only the categories of expense in the chart.
The chart looks better without those accounts.
Was this article helpful?