urlscan.io logo urlscan.io
SecurityTrails logo

learn.microsoft.com Open in urlscan Pro
2600:141b:13:7a6::3544  Public Scan

Back to summary
Submitted URL: https://click.info.microsoftemail.com/?qs=15025a0197c1d4529e8332d23e9651f816d668adf816c45afa076791a34ab62f868ec657e76bdb5c7a019aa11d3b...
Effective URL: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-excel-stunning-report?wt.mc_id=pbirm_eml_day1-Nurture
Submission: On August 11 via manual — Scanned from US

Form analysis 3 forms found in the DOM

Name: nav-bar-search-form — GET /en-us/search/

<form class="nav-bar-search-form" method="GET" name="nav-bar-search-form" role="search" id="nav-bar-search-form" aria-label="Search" action="/en-us/search/">
  <div class="autocomplete" data-bi-name="autocomplete"><!---->
    <div class="field-body control ">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="site-search-input" data-test-id="site-search-input" class="autocomplete-input input input-sm
						
						" type="search" name="terms" aria-expanded="false" aria-owns="ax-164-listbox" aria-controls="ax-164-listbox" aria-activedescendant="" aria-label="Search" aria-describedby="ms--site-search-input-description" placeholder="Search" pattern=".*">
      <span aria-hidden="true" class="icon is-small is-left" hidden="">
        <span class="has-text-primary docon docon-undefined"></span>
      </span>
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--site-search-input-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-164-listbox" data-test-id="site-search-input-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
  <!-- mobile safari will not dispatch submit event unless there's a submit button that is not display:none -->
  <button type="submit" class="visually-hidden" tabindex="-1" aria-hidden="true"></button>
  <input name="category" hidden="" value="">
  <input name="scope" hidden="" value="Power BI">
</form>

Name: nav-bar-search-form — GET /en-us/search/

<form class="nav-bar-search-form" method="GET" name="nav-bar-search-form" role="search" id="nav-bar-search-form-desktop" aria-label="Search" action="/en-us/search/">
  <div class="autocomplete" data-bi-name="autocomplete"><!---->
    <div class="field-body control has-icons-left">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="site-search-input-desktop" data-test-id="site-search-input-desktop" class="autocomplete-input input input-sm
						control has-icons-left
						" type="search" name="terms" aria-expanded="false" aria-owns="ax-165-listbox" aria-controls="ax-165-listbox" aria-activedescendant="" aria-label="Search" aria-describedby="ms--site-search-input-desktop-description" placeholder="Search"
        pattern=".*">
      <span aria-hidden="true" class="icon is-small is-left">
        <span class="has-text-primary docon docon-search"></span>
      </span>
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--site-search-input-desktop-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-165-listbox" data-test-id="site-search-input-desktop-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
  <!-- mobile safari will not dispatch submit event unless there's a submit button that is not display:none -->
  <button type="submit" class="visually-hidden" tabindex="-1" aria-hidden="true"></button>
  <input name="category" hidden="" value="">
  <input name="scope" hidden="" value="Power BI">
</form>

javascript:

<form action="javascript:" role="search" aria-label="Search" class="margin-bottom-xxs"><label class="visually-hidden" for="ax-40">Search</label>
  <div class="autocomplete display-block" data-bi-name="autocomplete"><!---->
    <div class="field-body control has-icons-left">
      <input role="combobox" maxlength="100" aria-autocomplete="list" autocapitalize="off" autocomplete="off" autocorrect="off" spellcheck="false" id="ax-40" data-test-id="ax-40" class="autocomplete-input input input-sm
						control has-icons-left
						width-full" type="text" aria-expanded="false" aria-owns="ax-41-listbox" aria-controls="ax-41-listbox" aria-activedescendant="" aria-describedby="ms--ax-40-description" placeholder="Filter by title" pattern=".*">
      <span aria-hidden="true" class="icon is-small is-left">
        <span class="has-text-primary docon docon-filter-settings"></span>
      </span>
      <span aria-hidden="true" class="autocomplete-loader loader has-text-primary " hidden=""></span>
      <span hidden="" id="ms--ax-40-description"> Suggestions will filter as you type </span>
    </div>
    <ul role="listbox" id="ax-41-listbox" data-test-id="ax-40-listbox" class="autocomplete-suggestions is-vertically-scrollable padding-xxs " aria-label="Suggestions" hidden="">
    </ul>
    <!---->
  </div>
</form>

Text Content

Skip to main content


This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security
updates, and technical support.

Download Microsoft Edge More info about Internet Explorer and Microsoft Edge
Documentation
Global navigation
 * Learn
   
 * Documentation
   
 * Training
   
 * Certifications
   
 * Q&A
   
 * Code Samples
   
 * Assessments
   
 * Shows
   
 * Events
   
 * More
     
   * Documentation
     
   * Training
     
   * Certifications
     
   * Q&A
     
   * Code Samples
     
   * Assessments
     
   * Shows
     
   * Events
     
   

Suggestions will filter as you type
Suggestions will filter as you type
Search
Sign in

 * Profile
 * Settings

Sign out
Power BI
   
 * Get started
     
   * Power BI get started documentation
     
   * What is Power BI?
     
   * Basic concepts
     
   * Learn Power BI
     
   * Individual sign-up
     
   
 * Work with data
     
   * Connect to data documentation
     
   * Transform, shape, and model data documentation
     
   * Data refresh
     
   * On-premises gateways
     
   * DAX basics
     
   * Create and manage relationships
     
   * Create measures
     
   
 * Create & share
     
   * Create reports and dashboards documentation
     
   * Collaborate, share, and integrate across products documentation
     
   * Get Power BI samples
     
   * Use report themes
     
   * Add conditional table formatting
     
   * Ways to share your work
     
   * Organize work in the new workspaces
     
   * Publish to web
     
   
 * Admin & enterprise
     
   * Admin documentation
     
   * Enterprise documentation
     
   * Purchase Power BI Pro licenses
     
   * What is Power BI Premium?
     
   * Row-level security (RLS)
     
   * Licensing the Power BI service
     
   
 * Developer
     
   * Power BI developer documentation
     
   * Embedded analytics documentation
     
   * Visuals documentation
     
   * Automation documentation
     
   
 * Resources
     
   * Products
       
     * Power BI Service
       
     * Power BI Desktop
       
     * Power BI Report Server
       
     * Power BI Report Builder
       
     * Power BI Mobile
       
     
   * Power BI training
       
     * Learn Power BI
       
     * Become a Power BI data analyst
       
     
   * Support
     
   * Community
     
   * Give Feedback
     
   * Release plan
     
   * Team blog
     
   
 * More
     
   * Get started
       
     * Power BI get started documentation
       
     * What is Power BI?
       
     * Basic concepts
       
     * Learn Power BI
       
     * Individual sign-up
       
     
   * Work with data
       
     * Connect to data documentation
       
     * Transform, shape, and model data documentation
       
     * Data refresh
       
     * On-premises gateways
       
     * DAX basics
       
     * Create and manage relationships
       
     * Create measures
       
     
   * Create & share
       
     * Create reports and dashboards documentation
       
     * Collaborate, share, and integrate across products documentation
       
     * Get Power BI samples
       
     * Use report themes
       
     * Add conditional table formatting
       
     * Ways to share your work
       
     * Organize work in the new workspaces
       
     * Publish to web
       
     
   * Admin & enterprise
       
     * Admin documentation
       
     * Enterprise documentation
       
     * Purchase Power BI Pro licenses
       
     * What is Power BI Premium?
       
     * Row-level security (RLS)
       
     * Licensing the Power BI service
       
     
   * Developer
       
     * Power BI developer documentation
       
     * Embedded analytics documentation
       
     * Visuals documentation
       
     * Automation documentation
       
     
   * Resources
       
     * Products
         
       * Power BI Service
         
       * Power BI Desktop
         
       * Power BI Report Server
         
       * Power BI Report Builder
         
       * Power BI Mobile
         
       
     * Power BI training
         
       * Learn Power BI
         
       * Become a Power BI data analyst
         
       
     * Support
       
     * Community
       
     * Give Feedback
       
     * Release plan
       
     * Team blog
       
     
   

 1. Buy now
 2. Open Power BI

Table of contents Exit focus mode

Search
Suggestions will filter as you type
 * Create reports and dashboards in Power BI
 * Overview
   * Tour the report editor
   * What are paginated reports?
 * Tutorials
   * Power BI Desktop: From Excel to report
   * Power BI Desktop: Dimensional model to report
   * Power BI service: From Excel to report
   * Explore a Power BI sample
   * Create a decomposition tree
   * Online course: Paginated reports
   * Create a paginated report and upload
 * Samples
 * Power BI reports
 * Visualizations
 * Artificial intelligence
 * Natural language Q&A
 * Metrics
 * Dashboards
 * Paginated reports
 * Deployment pipelines
 * Accessibility
 * Other tasks

Download PDF
    
 1. Learn
    
    
 2. Power Platform
    
    
 3. Power BI
    

    
 1. Learn
    
    
 2. Power Platform
    
    
 3. Power BI
    

Read in English Add
Table of contents Read in English Add Edit Print

Twitter LinkedIn Facebook Email
Table of contents


TUTORIAL: FROM EXCEL WORKBOOK TO STUNNING REPORT IN POWER BI DESKTOP

 * Article
 * 11/13/2022
 * 3 contributors

Feedback



IN THIS ARTICLE

    
 1. Prerequisites
    
 2. Get data
    
 3. Prepare your data
    
 4. Build your report
    
 5. Save your report
    
 6. Publish to the Power BI service to share
    
 7. Next steps
    

Show 3 more

APPLIES TO: Power BI Desktop Power BI service

In this tutorial, you build a beautiful report from start to finish in 20
minutes!



Your manager wants to see a report on your latest sales figures. They've
requested an executive summary of:

 * Which month and year had the most profit?
 * Where is the company seeing the most success (by country/region)?
 * Which product and segment should the company continue to invest in?

Using our sample finance workbook, we can build this report in no time. Here’s
what the final report will look like. Let’s get started!

In this tutorial, you'll learn how to:

 * Download sample data two different ways
 * Prepare your data with a few transformations
 * Build a report with a title, three visuals, and a slicer
 * Publish your report to the Power BI service so you can share it with your
   colleagues


PREREQUISITES

 * Before you start, you need to download Power BI Desktop.
 * If you're planning to publish your report to the Power BI service and you
   aren't signed up yet, sign up for a free trial.


GET DATA

You can get the data for this tutorial using one of two methods.


GET DATA IN POWER BI DESKTOP

When you open Power BI Desktop, select Try a sample dataset from the blank
canvas.



If you've landed on this tutorial from Power BI Desktop, go ahead and choose
Load data.




DOWNLOAD THE SAMPLE

You can also download the sample workbook directly.

 1. Download the Financial sample Excel workbook.
 2. Open Power BI Desktop.
 3. In the Data section of the Home ribbon, select Excel.
 4. Navigate to where you saved the sample workbook, and select Open.


PREPARE YOUR DATA

In Navigator, you have the option to transform or load the data. The Navigator
provides a preview of your data so you can verify that you have the correct
range of data. Numeric data types are italicized. If you need to make changes,
transform your data before loading. To make the visualizations easier to read
later, we do want to transform the data now. As you do each transformation, you
see it added to the list under Query Settings in Applied Steps

 1. Select the Financials table, and choose Transform Data.
    
    

 2. Select the Units Sold column. On the Transform tab, select Data Type, then
    select Whole Number. Choose Replace current to change the column type.
    
    The top data cleaning step users do most often is changing data types. In
    this case, the units sold are in decimal form. It doesn’t make sense to have
    0.2 or 0.5 of a unit sold, does it? So let’s change that to whole number.
    
    

 3. Select the Segment column. We want to make the segments easier to see in the
    chart later, so let’s format the Segment column. On the Transform tab,
    select Format, then select UPPERCASE.
    
    

 4. Let's shorten the column name from Month Name to just Month. Double-click
    the Month Name column, and rename to just Month.
    
    

 5. In the Product column, select the dropdown and clear the box next to
    Montana.
    
    We know the Montana product was discontinued last month, so we want to
    filter this data from our report to avoid confusion.
    
    

 6. You see that each transformation has been added to the list under Query
    Settings in Applied Steps.
    
    

 7. Back on the Home tab, select Close & Apply. Our data is almost ready for
    building a report.
    
    You see the Sigma symbol in the Fields list? Power BI has detected that
    those fields are numeric. Power BI also indicates the date field with a
    calendar symbol.
    
    


EXTRA CREDIT: WRITE AN EXPRESSION IN DAX

Writing measures and creating tables in the DAX formula language is super
powerful for data modeling. There's lots to learn about DAX in the Power BI
documentation. For now, let's write a basic expression and join two tables.

 1. On the Home ribbon, select New measure.
    
    

 2. Type this expression to add all the numbers in the Units Sold column.
    
    DAX Copy
    
    
    Total Units Sold = SUM(financials[Units Sold])    
    

 3. Select the check mark to commit.
    
    

 4. Now select the Data view on the left.
    
    

 5. On the Home ribbon, select New table.
    
    

 6. Type this expression to generate a Calendar table of all dates between
    January 1, 2013, and December 31, 2014.
    
    DAX Copy
    
    
    Calendar = CALENDAR(DATE(2013,01,01),Date(2014,12,31))    
    

 7. Select the check mark to commit.
    
    

 8. Now select Model view on the left.
    
    

 9. Drag the Date field from the financials table to the Date field in the
    Calendar table to join the tables, and create a relationship between them.
    
    


BUILD YOUR REPORT

Now that you've transformed and loaded your data, it's time to create your
report. In the Fields pane on the right, you see the fields in the data model
you created.

Let’s build the final report, one visual at a time.




VISUAL 1: ADD A TITLE

 1. On the Insert ribbon, select Text Box. Type “Executive Summary – Finance
    Report”.

 2. Select the text you typed. Set the Font Size to 20 and Bold.
    
    

 3. Resize the box to fit on one line.


VISUAL 2: PROFIT BY DATE

Now, you create a line chart to see which month and year had the highest profit.

 1. From the Fields pane, drag the Profit field to a blank area on the report
    canvas. By default, Power BI displays a column chart with one column,
    Profit.

 2. Drag the Date field to the same visual. If you created a Calendar table in
    Extra credit: Create a table in DAX earlier in this article, drag the Date
    field from your Calendar table instead.
    
    Power BI updates the column chart to show profit by the two years.
    
    

 3. In the Fields section of the Visualizations pane, select the drop-down in
    the X-axis value. Change Date from Date Hierarchy to Date.
    
    
    
    Power BI updates the column chart to show profit for each month.
    
    

 4. In the Visualizations pane, change the visualization type to Line chart.
    
    
    
    Now you can easily see that December 2014 had the most profit.


VISUAL 3: PROFIT BY COUNTRY/REGION

Create a map to see which country/region had the highest profits.

 1. From the Fields pane, drag the Country field to a blank area on your report
    canvas to create a map.

 2. Drag the Profit field to the map.
    
    Power BI creates a map visual with bubbles representing the relative profit
    of each location.
    
    
    
    Europe seems to be performing better than North America.


VISUAL 4: SALES BY PRODUCT AND SEGMENT

Create a bar chart to determine which companies and segments to invest in.

 1. Drag the two charts you've created to be side by side in the top half of the
    canvas. Save some room on the left side of the canvas.

 2. Select a blank area in the lower half of your report canvas.

 3. In the Fields pane, select the Sales, Product, and Segment fields.
    
    Power BI automatically creates a clustered column chart.

 4. Drag the chart so it's wide enough to fill the space under the two upper
    charts.
    
    
    
    Looks like the company should continue to invest in the Paseo product and
    target the Small Business and Government segments.


VISUAL 5: YEAR SLICER

Slicers are a valuable tool for filtering the visuals on a report page to a
specific selection. In this case, we can create two different slicers to narrow
in on performance for each month and year. One slicer uses the date field in the
original table. The other uses the date table you may have created for "extra
credit" earlier in this tutorial.

Date slicer using the original table

 1. In the Fields pane, select the Date field in the Financials table. Drag it
    to the blank area on the left of the canvas.

 2. In the Visualizations pane, choose Slicer.
    
    Power BI automatically creates a numeric range slicer.
    
    

 3. You can drag the ends to filter, or select the arrow in the upper-right
    corner and change it to a different type of slicer.

Date slicer using the DAX table

 1. In the Fields pane, select the Date field in the Calendar table. Drag it to
    the blank area on the left of the canvas.

 2. In the Visualizations pane, choose Slicer.

 3. In the Fields section of the Visualizations pane, select the drop-down in
    Fields. Remove Quarter and Day so only Year and Month are left.
    
    

 4. Expand each year and resize the visual, so all months are visible.
    
    
    
    We'll use this slicer in the finished report.

Now if your manager asks to see just 2013 data, you can use either slicer to
select years, or specific months of each year.


EXTRA CREDIT: FORMAT THE REPORT

If you want to do some light formatting on this report to add more polish, here
are a few easy steps.

Theme

 * On the View ribbon, change the theme to Executive.
   
   

Spruce up the visuals

Make the following changes on the Format tab in the Visualizations pane.



 1. Select Visual 2. In the Title section, change Title text to “Profit by Month
    and Year” and Text size to 16 pt. Toggle Shadow to On.

 2. Select Visual 3. In the Map styles section, change Theme to Grayscale. In
    the Title section, change title Text size to 16 pt. Toggle Shadow to On.

 3. Select Visual 4. In the Title section, change title Text size to 16 pt.
    Toggle Shadow to On.

 4. Select Visual 5. In the Selection controls section, toggle Show "Select all"
    option to On. In the Slicer header section, increase Text size to 16 pt.

Add a background shape for the title

 1. On the Insert ribbon, select Shapes > Rectangle. Place it at the top of the
    page, and stretch it to be the width of the page and height of the title.

 2. In the Format shape pane, in the Outline section, change Transparency to
    100%.

 3. In the Fill section, change Fill color to Theme color 5 #6B91C9 (blue).
    
    

 4. On the Format tab, select Send backward > Send to back.

 5. Select the text in Visual 1, the title, and change the font color to White.

Add a background shape for visuals 2 and 3

 1. On the Insert ribbon, select Shapes > Rectangle, and stretch it to be the
    width and height of Visuals 2 and 3.
 2. In the Format shape pane, in the Outline section, change Transparency to
    100%.
 3. In the Fill section, set the color to White, 10% darker.
 4. On the Format tab, select Send backward > Send to back.


FINISHED REPORT

Here's how your final polished report will look:



In summary, this report answers your manager’s top questions:

 * Which month and year had the most profit?
   
   December 2014

 * Which country/region is the company seeing the most success in?
   
   In Europe, specifically France and Germany.

 * Which product and segment should the company continue to invest in?
   
   The company should continue to invest in the Paseo product and target the
   Small Business and Government segments.


SAVE YOUR REPORT

 * On the File menu, select Save.


PUBLISH TO THE POWER BI SERVICE TO SHARE

To share your report with your manager and colleagues, publish it to the Power
BI service. When you share with colleagues that have a Power BI account, they
can interact with your report, but can’t save changes.

 1. In Power BI Desktop, select Publish on the Home ribbon.
    
    You may need to sign in to the Power BI service. If you don't have an
    account yet, you can sign up for a free trial.

 2. Select a destination such as My workspace in the Power BI service > Select.

 3. Select Open 'your-file-name' in Power BI.
    
    
    
    Your completed report opens in the browser.
    
    

 4. Select Share at the top of the report to share your report with others.
    
    


NEXT STEPS

 * Tutorial: Analyze sales data from Excel and an OData feed

More questions? Try the Power BI Community.





FEEDBACK

Submit and view feedback for

This product This page
View all page feedback

--------------------------------------------------------------------------------


ADDITIONAL RESOURCES

--------------------------------------------------------------------------------

Documentation

   

 * Download the Financial Sample Excel workbook for Power BI - Power BI
   
   Learn how to download the Financial Sample workbook for Power BI directly
   from this page or from Power BI Desktop.

   

 * Tutorial: From Excel workbook to a Power BI report in Microsoft Teams - Power
   BI
   
   This article shows how you can quickly create a stunning report in the Power
   BI service from an Excel workbook, and share it in Teams.

   

 * Power BI video series - Power BI
   
   Find video demos for how to work with Power BI, view and link directly to the
   latest videos, and find training and product updates.

   

 * How to be sure that content is up to date - Power BI
   
   Learn how Power BI ensures you're working with the latest version of the
   data, report, dashboard, and app.

   

 * Tutorial: Explore a Power BI sample - Power BI
   
   Learn to install and explore the Retail Analysis sample in the Power BI
   service and in Power BI Desktop.

   

 * Get started with Power BI Desktop - Power BI
   
   Get started with Power BI Desktop.

   

 * Power BI webinars - Power BI
   
   Register for upcoming Power BI live webinars or watch recorded sessions
   on-demand for features such as Getting started, Partners solutions, and much
   more.

   

 * Get samples for Power BI - Power BI
   
   Built-in samples and apps in the Power BI service, .pbix files, Excel
   datasets, and SQL databases, all to help you explore Power BI functionality.

   

Show 5 more



English (United States)
Theme
 * Light
 * Dark
 * High contrast

 * 
 * Previous Versions
 * Blog
 * Contribute
 * Privacy
 * Terms of Use
 * Trademarks
 * © Microsoft 2023


ADDITIONAL RESOURCES




--------------------------------------------------------------------------------

Documentation

   

 * Download the Financial Sample Excel workbook for Power BI - Power BI
   
   Learn how to download the Financial Sample workbook for Power BI directly
   from this page or from Power BI Desktop.

   

 * Tutorial: From Excel workbook to a Power BI report in Microsoft Teams - Power
   BI
   
   This article shows how you can quickly create a stunning report in the Power
   BI service from an Excel workbook, and share it in Teams.

   

 * Power BI video series - Power BI
   
   Find video demos for how to work with Power BI, view and link directly to the
   latest videos, and find training and product updates.

   

 * How to be sure that content is up to date - Power BI
   
   Learn how Power BI ensures you're working with the latest version of the
   data, report, dashboard, and app.

   

 * Tutorial: Explore a Power BI sample - Power BI
   
   Learn to install and explore the Retail Analysis sample in the Power BI
   service and in Power BI Desktop.

   

 * Get started with Power BI Desktop - Power BI
   
   Get started with Power BI Desktop.

   

 * Power BI webinars - Power BI
   
   Register for upcoming Power BI live webinars or watch recorded sessions
   on-demand for features such as Getting started, Partners solutions, and much
   more.

   

 * Get samples for Power BI - Power BI
   
   Built-in samples and apps in the Power BI service, .pbix files, Excel
   datasets, and SQL databases, all to help you explore Power BI functionality.

   

Show 5 more


IN THIS ARTICLE



English (United States)
Theme
 * Light
 * Dark
 * High contrast

 * 
 * Previous Versions
 * Blog
 * Contribute
 * Privacy
 * Terms of Use
 * Trademarks
 * © Microsoft 2023