{"id":2728,"date":"2019-02-01T16:18:54","date_gmt":"2019-02-01T16:18:54","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2728"},"modified":"2023-12-30T23:03:43","modified_gmt":"2023-12-30T23:03:43","slug":"using-excel-to-build-a-simple-dashboard","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/using-excel-to-build-a-simple-dashboard\/","title":{"rendered":"Using Excel to build a simple dashboard"},"content":{"rendered":"

Excel charts are great visual tools to show changes in data. You can build dynamic charts in Excel with a few easy steps. Below is an example of a table which can be used to build an interactive chart.<\/p>\n

\"Basic<\/a><\/p>\n

The Quantity<\/strong> column’s values are simply entered into the cells, but the Revenue<\/strong> figures are the result of the formula =B2*C2<\/strong>, which is copied down the column. The Selling Price<\/strong> is generated by multiplying the Base Price<\/strong> (see below) with the Price Change<\/strong> showing in the drop-down list in H2<\/strong> (also shown below, but more about the drop-down list later). The formula is: =F2+F2*$H$2<\/strong>. The $<\/strong> symbols in the formula are used to lock the percentage in the formula when you copy it down, because it is only in the one cell.<\/p>\n

\"Base<\/a><\/p>\n

Select cell H2<\/strong>. To create the drop-down list, you need to have a normal list entered into some cells beforehand, as shown below. The drop-down is created by clicking the Data<\/strong> tab, then clicking Data Validation<\/strong>. In the box, select List<\/strong> from the Allow<\/strong> drop-down. Next, click inside the Source<\/strong> field, then select the list of figures in the cells (J2:J8<\/strong> in this example). Now you have a drop-down list in cell H2<\/strong>.<\/p>\n

\"Drop-down\"<\/a><\/p>\n

As shown earlier, the figure shown in the drop-down cell (H2<\/strong>) is linked into the formula calculating the Selling Price<\/strong>. You can now change the Selling Price<\/strong> using the drop-down. And because the Selling Price is linked to the Revenue<\/strong> by a formula, these numbers also change!<\/p>\n

The final step is inserting a chart which shows the Regions<\/strong> and their Revenues<\/strong>. To do this, first select the regions (including the label). Then hold down the Ctrl button and also select the revenue figures, again including the label.<\/p>\n

\"Select<\/a><\/p>\n

Now click the Insert tab, then click the Clustered Column option. This will insert a clustered column chart into your spreadsheet.<\/p>\n

\"Charts\"<\/a><\/p>\n

Move the chart to a suitable spot in your sheet, then test the drop-down list. See the magic happen as the chart changes when you select different values in the drop-down list!<\/p>\n

\"Final\"<\/a><\/p>\n

Excel course is one of our Microsoft Office 365 training<\/a> courses.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Excel charts are great visual tools to show changes in data. You can build dynamic charts in Excel with a few easy steps. Below is an example of a table which can be used to build an interactive chart. The Quantity column’s values are simply entered into the cells, but the Revenue figures are the […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[637,6,8,9,18],"tags":[],"class_list":["post-2728","post","type-post","status-publish","format-standard","hentry","category-application","category-hints-tips","category-microsoft","category-microsoft-office","category-technology"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2728"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2728\/revisions"}],"predecessor-version":[{"id":3734,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2728\/revisions\/3734"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}