@WonderLaura

Last week, we had a customer ask about Power BI. We had a chat about what the Power BI need was, so that we would know how complex of a project it would be, and what it would entail. The main reason for Power BI, they said, was the need for dashboards for executives and managers to look at and discuss, in their leadership meetings. I asked about the data, and basically it comes from a few different databases and systems, and they currently consolidate it all in spreadsheets with charts. Many spreadsheets with many charts within each spreadsheet. They mentioned not having a need to connect to the databases directly with Power BI, but really just to have all of those charts in one simple dashboard, or dashboards per region, so that their meetings would be more efficient, and wouldn’t have to entail opening multiple spreadsheets each time. It only requires SharePoint and the file and media web part.

This gave me an idea! From the requirements, instead of spending thousands of dollars potentially, and many consulting hours re-building the charts from the spreadsheets, in Power BI, we could simply display the existing charts as web parts, and use a SharePoint page as the “dashboard”!

Here is a simple guide to display your data in a visual way, using just Excel spreadsheets and SharePoint. No extra cost or premium features, and perfect for small and medium sized businesses. My Microsoft license level is E1 in this example.

In this post, I will show you how to use the SharePoint File and Media web part to display charts from an Excel spreadsheet on your SharePoint site. This is a great way to showcase your data in a visual way, without losing any functionality or security of your original file.

The File and Media web part in SharePoint lets you embed files from your document library or OneDrive on your site pages. You can use it to show documents, images, videos, PDFs, and more. You can also use it to show Excel spreadsheets, and it doesn’t have to be the the whole file, but specific parts of it, such as charts, tables, or ranges.

This post will focus on how to use the File and Media web part to show charts from an Excel spreadsheet. Charts are a powerful tool to communicate data and trends, and they can make your spreadsheet more attractive and easy to understand. Once you have your charts ready in Excel, you can use the File and Media web part to show them on your SharePoint site. This way, you can share your data with your audience in a dynamic and interactive way, without having to upload your file to a different platform or use any code. You can also update your charts in Excel and see the changes reflected on your site automatically.

Ready to learn how to do it? Let’s get started!

Step 1: Create your charts in Excel

The first step is to create your charts in Excel. You can use any version of Excel that you have, as long as you save your file in a format that is compatible with SharePoint, such as .xlsx, .xlsm, or .xlsb. You can also use Excel Online, which is part of Microsoft 365 and lets you create and edit spreadsheets in your browser. I’m using a demo file called Marketing Compensation Report, which looks like this:

To create a chart in Excel, you need to have some data in your spreadsheet. You can use any type of data that you want, as long as it is organized in rows and columns. For example, you can use sales data, survey results, budget numbers, or project timelines. You can also use formulas to calculate values or perform analysis on your data.

Once you have your data ready, you can select the cells that you want to use for your chart. You can select a single cell, a range of cells, a table, or a pivot table. You can also select multiple ranges of cells from different worksheets or workbooks, as long as they have the same structure and labels.

After selecting your data, you can go to the Insert tab on the ribbon and choose the type of chart that you want to create. You can choose from different categories, such as column, line, pie, bar, area, scatter, or combo. You can also use the Recommended Charts button to see some suggestions based on your data.

When you insert a chart, Excel will create it on the same worksheet as your data, or on a new worksheet if you prefer. You can then customize your chart with different options, such as style, color, layout, title, legend, axis, gridlines, data labels, and more. You can also add or remove data series, change the chart type, or apply filters to your data.

You can create as many charts as you want in your spreadsheet, using different types of data and chart types. You can also move, resize, copy, or delete your charts as you wish. You can also create a chart sheet, which is a separate worksheet that only contains a chart. This can be useful if you want to show a large or complex chart.

Here is an example of a spreadsheet with some charts that I created in Excel:

This worksheet has 5 tabs, and I’m showing the first tab here. As you can see, I have four charts on the same tab. The data is on another tab. I have also customized my charts with different styles, colors, layouts, and elements. For example,

Now that I have my charts ready in Excel, I can save my file and upload it to my SharePoint document library or OneDrive.

Step 2: Upload your file to SharePoint or OneDrive

The next step is to upload your file to SharePoint or OneDrive. You can use any SharePoint site or OneDrive account that you have access to, as long as you have permission to upload files and edit pages. You can also use the same site or account where you want to show your charts, or a different one if you prefer. Keep in mind that if you upload to OneDrive, the people who will need to look at these charts will need to be given permission to that file. I recommend using SharePoint.

To upload your file to SharePoint, you can go to the document library where you want to store your file and click on the Upload button. You can then browse your computer or device and select your file. You can also drag and drop your file from your computer or device to the document library.

When you upload your file, SharePoint or OneDrive will automatically assign a URL to your file. This URL is the web address that you can use to access your file online. You will need this URL later to show your charts on your SharePoint site.

Here is an example of a file that I uploaded to my SharePoint document library:

As you can see, I have uploaded my file to a document library. The file name is Marketing Compensation Report.xlsx. You can also see a preview of the file and some information about it, such as the size, date, and owner.

Now that I have uploaded my file to SharePoint, I can go to the site page where I want to show my charts and edit it.

Step 3: Edit your site page and add the File and Media web part

The final step is to edit your site page and add the File and Media web part. You can use any site page that you have access to, as long as you have permission to edit it. You can also create a new site page if you want. I’m creating a new blank page using the New button on my site’s home page.

To edit your site page, you can go to the page and click on the Edit button. You can then add, remove, or rearrange web parts on your page. Web parts are the building blocks of your page, and they let you add different types of content and functionality to your page.

To add the file and media web part, you can click on the plus sign (+) on your page and select the File and media web part from the list. You can then configure the web part to show your file.

To configure the web part, you need to enter the URL of your file in the File or embed link box. You can copy and paste the URL from your SharePoint document library or OneDrive, or you can click on the Browse button and select your file from there. You can also click on the Change file button to change your file later.

When you pick your file, the web part will automatically detect the file type and show a preview of your file. Here is an example of a site page with the file and media web part showing my file:

As you can see, I have added the file and media web part to my site page and selected my file. The web part is showing a preview of my file, and I can see the whole spreadsheet with the data and the charts.

However, I don’t want to show the whole spreadsheet on my site page, I only want to show specific charts. How can I do that? This is where the file and media web part has a cool feature that lets you show parts of your file, such as charts, tables, or ranges. Let me show you how to use it.

Step 4: Show specific parts of your file using the File and Media web part

To show specific parts of your file using the file and media web part, you need to use a special syntax in the URL of your file. This syntax lets you specify the name of the chart, table, or range that you want to show on your site page. You can also use this syntax to show multiple parts of your file at once, or to show parts of your file from different worksheets or workbooks.

To use this web part, you need to know the name of each chart, table, or range that you want to show. You can find the name of the chart, table, or range in Excel, by selecting it and looking at the name box on the left side of the formula bar. You can also rename the chart, table, or range in Excel, by clicking on the name box and typing a new name.

Here is an example of how to find the name of a chart in Excel:

As you can see, I have selected the column chart on my spreadsheet, and the name box shows that the name of the chart is Design vs Content chart. I can also rename the chart by clicking on the name box and typing a new name, such as Sales by Month.

Once you know the name of the chart, table, or range that you want to show, this is used in the web part properties. Here is an example of the settings in the file and media web part:

As you can see, I have selected Chart from the drop-down, and manually typed the name of the chart that I’d like to display.

When I add the syntax to the URL of my file, the web part will update the preview of my file and show only the part that I specified. I can then adjust the size and position of the web part on my page. I can also use the toolbar to zoom in or out, download, print, or open my file in a new tab.

Here is an example of how I added the web part to my page twice, and the web part shows only the chart that I specified in each web part. I added these two web parts to a 2 column section on the page, next to each other.

I can repeat the same process to show other charts, tables, or ranges from my file on my site page. I can also show multiple parts of my file at once, or show parts of my file from different worksheets or workbooks.

As you can see, I have added two more file and media web parts to my site page. Now I have a site page with four charts from my file, showing different aspects of my data. I can also add some text or other web parts to my page to provide some context or explanation for my charts. I can also update my charts in Excel and see the changes reflected on my site page automatically. Notice that I’ve added this new page, Charts, to my left navigation. For the concept of having multiple dashboards for, say, different departments, I can add multiple pages to my site, each with a few of these web parts, and name each page after the region. Then, as you can see this page name being displayed down the left, all pages will be listed, for end users to be able to quickly switch between them.

Conclusion

In this blog post, I have shown you how to use the SharePoint file and media web part to show charts from an Excel spreadsheet on your SharePoint site. This is a simple and effective way to display your data in a visual way, without losing any functionality or security of your original file, and without requiring Power BI.

By using the file and media web part, you can:

• Edit your site page and add the file and media web part to show your file.
• Show specific parts of your file, such as charts, tables, or ranges.
• Show multiple parts of your file at once, or show parts of your file from different worksheets or workbooks.
• Update your charts in Excel and see the changes reflected on your site page automatically.

I hope you found this helpful! If you have any questions or feedback, please leave a comment below. If you want to learn more about the file and media web part or other SharePoint features, I have created a SharePoint Site Design & Web Parts online course.