This article shows more details of publishing Excel reports from Excel to SharePoint. Frequently asked questions are also answered. Please read Part 1 and Part 2 of this article series before reading this article.
Estimated reading time 10 min
In the first two articles we have seen how to decide which parts of Excel to display and then save the file to SharePoint. We also saw how the reports are seen on browser itself.
Even if the report is shown on a browser, it is interactive. It allows you to filter things, use slicers and even change the structure of the report on the browser itself.
All this interactivity is temporary – that means the original file is not affected by the changes made while viewing the report. This facility only benefits the viewers.
What happens next month?
“Next month” here actually means the next time you need to refresh the report – depending upon the reporting frequency.
You will get more raw data periodically and you will need to update the report. That is easy. Just open the report from SharePoint and append the additional data, refresh the pivot tables and charts. Save the file. That’s it.
What do I send next month?
Usually, you would have attached the new report and sent a mail with CCs. But this time, the file link has not changed – only the data has changed. Therefore, second time onwards, you do not send anything. Those who need to view the report already have the link.
How will others know that you have updated the report?
This is a genuine question. You are not going to send them anything. They will have the original mail with the link to the file. But by next month they will not remember the link. They will have to search for the mail and then click on the link.
But when do they click on the link? They don’t know when you refreshed the report.
Don’t worry. SharePoint has a solution for this need as well. Instead of you having to remind and notify others that the report is ready, SharePoint will do it.
You have to teach the recipients of the report this feature. Ask them to select the Excel file in SharePoint and then click on the Files tab and choose Alert Me – Set alert on this document. Choose the desired settings and click OK.
Now onwards, SharePoint will send a notification whenever the file is changed. The responsibility has shifted from a person to SharePoint.
How will I see previous month report?
Usually, we save the file as a snapshot for each month (or whatever frequency you work at), by changing the file name. So you will have 12 different files at the end of the month.
Fortunately, you are no longer required to do this tedious task of changing file names and storing snapshots manually. SharePoint will do this for you.
Just make sure that Versioning is enabled in the document library where you are storing the reports. If you don’t know how to enable versioning, ask your IT team to do it.
Go to Library tab – Library Settings – Versioning Settings and activate versioning.
Once this is done, SharePoint will automatically store the previous version whenever you make changes to a file.
How to see backdated reports?
Once versioning is done, anyone with permissions can see backdated reports easily. Click on the ellipsis (… three dots) next to filename. Now a preview window opens – that also has another set of three dots .. click those. Now a menu appears – choose Version History.
Not very intuitive – but that is what we have (it is a part of “Modern UI”)
Now all the past versions are shown. Remember, the file name DOES NOT change. Only the version number changes. Choose the one you want and view it.
If there was a mistake in the latest version of the report and the previous one was more correct, you can make an older version the current one – choose the Restore option.
Part 1 covers the concept.
Part 2 shows the step by step process.
Part 3 (this article) covers Frequently Asked Questions.
There is much more to discuss. We have just see ONE Excel file as a report. Usually there are more than one reports to be viewed at a glance. Now a single page dashboard can be with reports picked up from different Excel files. We will see this type of dashboard creation in the next article. We will also compare this dashboard with Power View – a more powerful and easy to create dashboard feature introduced in 2013.