This article explains how to create a report in Excel which can update automatically from the live data over the web.
"Live-linked" reports can be based on reports you have created in Analytics or on pivot reports you have created in the Backoffice.
- In Analytics: just right-click on the Live link menu option button at the bottom of your report and choose the option to Copy link address/shortcut to obtain the link you need
- For a Pivot report in the Backoffice: right click on the Excel icon in the pivot report list and choose the option to Copy link address/shortcut to obtain the link you need
Export the data to Excel
Once you've copied the link to your clipboard, open your (new) Excel document and in the main menu, go:
Excel 2003: Data > Import External Data > New Web Query
Excel 2007:
Excel 2010 - Office 365 Data > From Web
Paste the shortcut you copied into the Address field and click Go.
IMPORTANT: You will be prompted to enter log in details - please use the same credentials you use for the back office. You might be asked to enter the log in twice - please follow the instructions.
Note: There is a known Microsoft issue in Excel 2016/Office 365, so you may need to enable a legacy 'Get data from web' feature - see this article for more information.
You should see the data from your view in the window as indicated below:
Click the second yellow arrow to select the data (in the Ref column heading cell above) - the yellow arrow will change to a green tick.
Click Import to import the data.
Note that you may see some message and warning screens appear, e.g. whether you want to view the certificate or whether you want to display only the secure data, etc.
Once past these screens, you will be asked where you want the data to be displayed. Choose cell A1 in your Existing worksheet, unless you specifically don't want the data to be entered here.
You should now see the data in your Excel worksheet and can Save and Close if you wish.
Step 4 - Updating the data
Open the Excel report and just right click anywhere in the data.
Select the Refresh Data (red exclamation mark icon) option at the bottom to update the data.
Note that you must be connected to the Internet to be able to update the data.
Alternatively, if you turn on the External Data toolbar in Excel (View > Toolbars > External Data), you can also click the Refresh or Refresh All icons in the toolbar to update the data.
Advanced options
1. Refreshing data on file open
You can also set the report to refresh automatically each time you open it.
To do this, higlight a cell within your data and go back to the Data menu in the main menu bar and go: Data > Import External Data > Data Range Properties. (See screenshot below.)
Check the Refresh data on file open box if you want the report to update automatically each time you open it.
2. Add additional columns of data calculated from the columns in the raw data.
e.g. for a time vacancy open calculation, you might want to do Closing minus Opening date.
Tick the Fill down formulas in columns adjacent to data box on the Data Range Properties screen.
Now you can enter a new column heading in the top cell of the column to the right of your raw data and enter a formula in the cell below, e.g. =C2-B2.
Note that you only need to add the column heading and the first calculation.
The next time you refresh the raw data, the additional calculated column will also fill down with the relevant value in each cell for each row.
This function can be used to calculate or display a wide variety of additional information, based on your raw data.
Some useful examples are given below.
- Subtract two dates to get the total number of days difference between the dates:
=C2-B2 - Divide the above by 7 to get the number of weeks:
=(C2-B2)/7 - Calculate the number of working days between two dates (i.e. excluding weekends):
=NETWORKDAYS(A1,B1) - Calculate the number of working days between two dates, taking account of holidays:
=NETWORKDAYS(A1,B1,Holidays)
N.B. "Holidays" in the formula above is a reference to a named range in the same Excel file, which is a list of holiday dates. To set a named range called "Holidays", select the column you wish to enter all the holiday dates in and enter the word "Holidays" into the box to the left of the formula bar as shown below, where columns A is the named range: - Display a column showing only the month, e.g. Oct, from a full date field:
=TEXT(H2, "mmm") - Join two cells into one cell - e.g. to get Full name from First name and Surname:
= B2&" "&C2 or =CONCATENATE(B2," ",C2)
N.B. " " puts a space. - Display the first part of a value from another cell, e.g. where a | character is used to split a hierarchical list, such as Division X | Sub-division Y and you want to display just Division X:
= LEFT(A2, SEARCH(" | ", A2)-1) -
Display True or False if a date is within a certain range, e.g. where date1 and date2 are your date range:
=IF(AND(A2<=date1,A2>=date2),TRUE, FALSE)
N.B. date1 and date2 could also be references to other cells. -
Display a date-time stamp (eg 13/10/2009 00:00:00) as a date in another column:
=TEXT(A2,"dd/mm/yyyy") -
Calculation to identify duplicate entries or to count unique entries only (e.g. in a cumulative report)
=IF(SUMPRODUCT(($A2:$A102=A2)*($B2:$B102=B2))>1,0,1)
A is the data you want to use as the unique identifier (e.g. candidate email, vacancy id)
B is the column you want to pick up duplicate entries in (e.g. folder move, vacancy status change).
N.B. This formula assumes that your raw data is ordered by the column you are using as the unique identifier and that a duplicate entry is unlikely to occur in more than 100 rows below the first occurence.
Care when editing reports with associated live-linked reports
When you edit reports that are used for reports in this way, take care, as you may end up breaking your reports, particularly where you have used the Fill down formulas in columns adjacent to data feature.
You may need to amend your Excel report to reflect the change you made to your view/report in the system.