{"id":8089,"date":"2018-06-07T12:19:45","date_gmt":"2018-06-07T12:19:45","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=8089"},"modified":"2018-06-07T12:19:45","modified_gmt":"2018-06-07T12:19:45","slug":"sales-dashboard","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/sales-dashboard\/","title":{"rendered":"Sales Dashboard"},"content":{"rendered":"

Hi All,<\/p>\n

Dashboard-<\/u><\/strong>\u00a0Dashboard is one platform that helps management in tracking Performance, evaluation and help them to take a decision based on it. A dashboard is often called a report, however, not all reports are in the dashboard.<\/p>\n

Now that we understand what a dashboard is, let\u2019s dive in and learn how to create a dashboard in Excel.<\/p>\n

Creating an Excel Dashboard is a multi-step process, once you come to know the idea of what you want to create, next step would get data and create it. If you have a different format of excel file i.e. CSV, Notepad etc. you can easily convert these files into excel. It is a onetime exercise only, where you simply copy and paste the data and it would be automatically updated.<\/p>\n

Let\u2019s start with a dummy data which can be download from here.<\/p>\n

As you can see below we have an Excel workbook in which we are maintaining sales, product and customer data on the different sheets.<\/p>\n

\"Pic-1\"<\/p>\n

\"pic-2\"<\/p>\n

\"pic-3\"<\/p>\n

In the end, we will create a Sale Dashboard as shown below-<\/p>\n

\"Dashboard\"<\/p>\n

Isn\u2019t cool?<\/p>\n

So, let\u2019s get started.<\/p>\n

Step1-<\/strong>\u00a0Firstly Convert your Data into table format-<\/p>\n

Put your cursor anywhere on your data->Insert->Table or you can also press a shortcut key Ctrl+T<\/p>\n

Note: – Once you click on Table option must be marked on \u201cMy table has headers\u201d, if you forget to click, additional column1..column2..will be added on the top of your table.<\/em><\/p>\n

\"pic-4\"<\/p>\n

Step2:-<\/strong> When you convert your data into the table format, now you can see there are a \u201ctable tools\u201d in ribbon tab in which you have to define your table name.<\/p>\n

Table Tools-> Table Name-> Define a Name E.g. Sales<\/p>\n

Note:\u00a0\u00a0Define table name will help us to identify, which sheet belongs to which name.<\/em><\/p>\n

\"pic-5\"<\/p>\n

For the rest two sheet Follow the same step1 and step2 to convert into the table and define a Name I.e. Product & Customer.<\/p>\n

Step3-<\/strong> In this workbook, we need to calculate Total Net sales, so we must build-up a relationship among the sheets, however, we must identify the common heading in between sheets which you can see in below images-<\/p>\n

Customer ID reflecting in Sales Sheet and ProductID too, so common sheet will be Sales Sheet.<\/p>\n

\"pic-6\"<\/p>\n

How to make a relationship between sheets?<\/p>\n

Go to Data Tab-> in Data Tools Group -> Relationship<\/p>\n

\"pic-9\"<\/p>\n

Click on New Tab as shown in below image-<\/p>\n

\"pic-10\"<\/p>\n

Table box- Select Sales and column(Foreign)- Customer ID<\/p>\n

Related Table- Customer sheet and Related column (Primary)- Customer Id<\/p>\n

\"pic-11\"<\/p>\n

Follow the same as defined in image given below->OK<\/p>\n

\"pic-12\"<\/p>\n

\"pic-13\"<\/p>\n

 <\/p>\n

Step4-<\/strong>\u00a0 Upload your data in Pivot table \u2013<\/p>\n

How to upload data into pivot table-<\/p>\n

Go to -> Insert-> Pivot table or You can press a shortcut key alt+N+V<\/p>\n

\"pic-14\" \"pic-15\"<\/p>\n

You must have checked on the option\u201d Use this Workbook\u2019s Data Model\u201d.<\/p>\n

What is the use of \u201cUse this Workbook\u2019s Data Model\u201d?<\/p>\n

A Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Power\u00a0Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily-<\/p>\n

This option can be used by Ms-office 2013 user or upper. Click and\u00a0Download the Power Pivot<\/a><\/p>\n

Step5<\/strong>: –\u00a0\u00a0with the help of \u201cData Model\u201d Instead of doing, again and again, it happened at one time, you can see in below image-<\/p>\n

\"Pic-16\"<\/p>\n

Step 6<\/strong>: –\u00a0 We need to calculate Total Net sales in which we have given Quantity (ref. sales sheet) and Price (ref. Product sheet), so we must calculate Total Quantity, Total Price so that we can easily calculate Net sales.\"pic-17\"<\/p>\n

Step7<\/strong>: How to do the calculation in the pivot table?<\/p>\n

Right click on data table-> Add Measure<\/p>\n

\"pic-19\"<\/p>\n

In this window select table name\u00a0from\u00a0drop down\u00a0list i.e. sales-> define Name E.g. Total Quantity -> value description (optional)-> type a formula to add total quantity i.e.=sum([Quantity])-> check DAX formula-> Ok<\/p>\n

DAX formula-<\/em><\/strong>\u00a0DAX is a formula language. You can use DAX to define custom calculations for Calculated Columns and for Measures (also known as calculated fields). DAX includes some of the functions used in Excel formulas, and additional functions designed to work with relational data.<\/em><\/p>\n

\"Pic-20\"<\/p>\n

Follow the same step6 to calculate Total Quantity and Total Net sales, the reference image is given below-<\/p>\n

\"Pic-21\" \"pic-22\"<\/p>\n

Step8:-<\/strong> Drag fields- The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas.<\/p>\n

    \n
  1. Area to the Rows area<\/li>\n
  2. Net sales to the Values area<\/li>\n<\/ol>\n

    \"pic-23\"<\/p>\n

    Step9:<\/strong> -Now select your pivot table (Ctrl+Shift+*)-> copy (Ctrl+C)-> Paste (Ctrl+V) –<\/p>\n

      \n
    1. Category to Rows Area<\/li>\n
    2. Net sales to Value<\/li>\n<\/ol>\n

      \"pic-24\"<\/p>\n

      Follow the steps9-<\/p>\n

        \n
      1. Profession to Rows area<\/li>\n
      2. Net sales to Value<\/li>\n<\/ol>\n

        \"pic-25\"<\/p>\n

        Follow the step9-<\/p>\n

          \n
        1. Gender to Rows area<\/li>\n
        2. Net sales to Value area<\/li>\n<\/ol>\n

          \"pic-26\"<\/p>\n

          Step10<\/strong>:- Now insert a new sheet and rename it \u201cDashboard\u201d -> Insert four different<\/strong> blank charts as given below-<\/p>\n

          How to insert chart?<\/p>\n

          Insert tab-> charts group -> chart<\/p>\n

          \"pic-27\"<\/p>\n

          Step11<\/strong>:- Select chart (2D-Column chart)- Chart tools in ribbon tab->Design Tab-> Select Data-> Click Chart data Range and select the data from sheet5 (Given below)<\/p>\n

          \"pic-28\"<\/p>\n

          Select the data-><\/p>\n

          \"Pic-29\"<\/p>\n

          Import data in to chart one by one and create four chart as look like below-<\/p>\n

          \"pic-30\"<\/p>\n

          Edit the chart- In ribbon tab chart tools option-> design tab-><\/p>\n

            \n
          • You can apply different chart styles<\/li>\n
          • You can apply default chart layout<\/li>\n
          • Right upper corner->\u201d+\u201d ( Called chart elements)<\/strong>-> you can apply Titles, Data Labels, Legend etc.<\/li>\n
          • Click on format-> apply a color full formatting<\/li>\n<\/ul>\n

            \"Pic-31\"<\/p>\n

            Step12<\/strong>: Also, you can hide Filed button \u2013<\/p>\n

            Right, click-> Hide all field button on the chart-<\/p>\n

            \"pic-32\"<\/p>\n

            Format your chart-<\/strong> Right click on chart-> Fill option or Format chart area -> you will get a new window right side of your chart and apply chart formatting as you want.<\/p>\n

            \"pic-33\"<\/p>\n

            Step13<\/strong>: – How to highlight the image into the chart?<\/p>\n

            Right,\u00a0click on chart->Picture-> either you can select from your file or you can directly download it online. See below-<\/p>\n

            \"pic-35\"<\/p>\n

            \"pic-36\"<\/p>\n

            We will find picture online-> Online Pictures-> Search your desire picture-> click on import (see image below)<\/p>\n

            \"pic-37\"<\/p>\n

             <\/p>\n

            Select picture-> Import<\/p>\n

            For another image follow the same steps.<\/p>\n

            Finally, your dashboard will look like this-<\/p>\n

            \"pic-38\"<\/p>\n

            Step14<\/strong>: – Insert Slicer \u2013 Slicers provide buttons that you can click to filter table\u00a0data\u00a0or PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable.<\/p>\n

            Chart Tools-> Analyze-> Insert Slicer-> Select the heading which you want to Filter E.g. Area, City, Gender, and Category<\/p>\n

            \"Pic-39\"<\/p>\n

            Create a Report connection between slicers -> Slicer-> Option in ribbon tab-> Report connection<\/p>\n

            Or Right click on slicer and select \u201cReport connection\u201d<\/p>\n

            \"pic-40\"<\/p>\n

            Arrange all slicer into one Group->\u00a0\u00a0Select all slicer with holding Ctrl Key-> Format-> Group<\/p>\n

            \"pic-41\"<\/p>\n

            Now your finally Sales Dashboard has been created successfully. In Slicer when you click on any button, you will see chart will update accordingly.<\/p>\n

            \"Dashboard\"<\/p>\n

            You can download the practice file to click on this link-\u00a0Sales Data<\/p>\n

            Happy Learning \ud83d\ude42<\/p>\n

             <\/p>\n

             <\/p>\n

             <\/p>\n

             <\/p>\n

            <\/div>\n

            <\/path><\/svg><\/i> \"Loading\"<\/p>\n

            <\/div>\n","protected":false},"excerpt":{"rendered":"

            Hi All,
            \nDashboard-\u00a0Dashboard is one platform that helps management in tracking Performance, evaluation and help them to take a decision based on it. A dashboard is often called a report, however, not all reports are in the dashboard.
            \nNow that <\/p>\n

            <\/div>\n

            <\/path><\/svg><\/i> \"Loading\"<\/p>\n

            <\/div>\n","protected":false},"author":1,"featured_media":8131,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[265,266,263,243,264,257,260,259,249,256,248,244,250,252,245,253,254,246,251,267,255,262,258,247,261],"class_list":["post-8089","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=8089"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8089\/revisions"}],"predecessor-version":[{"id":8132,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8089\/revisions\/8132"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/8131"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=8089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=8089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=8089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}