Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                
Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    Only $11.99/month after trial. Cancel anytime.

    Excel BI and Dashboards in 7 Days: Build interactive dashboards for powerful data visualization and insights (English Edition)
    Excel BI and Dashboards in 7 Days: Build interactive dashboards for powerful data visualization and insights (English Edition)
    Excel BI and Dashboards in 7 Days: Build interactive dashboards for powerful data visualization and insights (English Edition)
    Ebook328 pages2 hours

    Excel BI and Dashboards in 7 Days: Build interactive dashboards for powerful data visualization and insights (English Edition)

    By Jared Poli

    ()

    Read preview

    About this ebook

    Everyone thinks of Excel differently, and its full potential is often untapped. Businesses tend to decide to invest heavily in proper BI tools, perhaps wrongfully assuming that Excel has a minimal role in that industry.

    Excel can be used effectively to collect, refresh, transform, and visualize your data in beautiful and eye-catching ways. This book covers building those skills and unlocking Excel and your potential in just seven days. The book explores the process of cleaning your data to ensure accuracy, using formulas to enhance and prepare the same for PivotTables. It will also help you understand how to use data visualization to create clear charts to communicate insights effectively and construct interactive dashboards for user exploration, including elements like slicers and timelines. The book also dives into discovering design principles for easy-to-understand dashboards, while gaining knowledge on maintaining and updating them for ongoing usability.

    Understanding the full power behind Excel will allow you to improve your spreadsheet game and prove that you can do it all with one industry standard tool and this book.
    LanguageEnglish
    PublisherBPB Online LLP
    Release dateApr 25, 2024
    ISBN9789355517425
    Excel BI and Dashboards in 7 Days: Build interactive dashboards for powerful data visualization and insights (English Edition)

    Related to Excel BI and Dashboards in 7 Days

    Related ebooks

    Enterprise Applications For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      What is ELT?

      Apr 1, 2021

      It stands for extract, load, and transform- the processes a data pipeline uses for replicating the data from a source system into a target system such as a cloud data warehouse. 1. Extraction is the first step in which data is copied from the source

      6 min read

    • Article

      Cutting Edges

      Feb 6, 2024

      Living in the open source world means if you want to sit on the cutting edge of development, you can. Nightly builds, compiling the kernel from source, grabbing Git repositories, if you want to try the latest, no one is going to try and stop or gatek

      1 min read

    • Article

      Getting The edge

      Feb 25, 2021

      Getting The edge
      7 min read

    • Article

      Customer-centric From Its Core

      Sep 16, 2018

      What’s been the biggest change you have seen in your career? I think one of the biggest things I am seeing recently is data becoming more and more important in how businesses operate and how they deliver customer experiences. Even a few years ago whe

      2 min read

    • Article

      Growing Communities of Practice

      Sep 1, 2019

      According to the SAP Digital Transformation Executive Study, 80 per cent of companies that have embraced digital transformation have experienced increased profitability. So why have only 21 per cent of companies completed their digital transformation

      5 min read

    • Article

      The Era of Human + Machine Innovation

      Jan 1, 2019

      Interview by Karen Christensen In today's environment, organizations that don't keep up with customers' evolving needs are doomed. What is the best way to get a handle on these evolving needs? The first step in understanding your customers is to acce

      5 min read

    • Article

      Nail Your Dream Job

      Jul 28, 2021

      Nail Your Dream Job
      7 min read

    • Article

      Feature

      May 22, 2022

      Feature
      4 min read

    • Article

      A Compass for Chief Executive Officers and Chief Learning Officers

      Aug 1, 2022

      Based on a 5 year study of the executive development industry, comprising interviews with over 200 executives, CLO's, CEO's, program designers and program developers, we have ascertained that the executive development field is at a critical crossroad

      18 min read

    • Article

      A Fresh Approach

      Mar 22, 2019

      Shekhar A Bhattacharjee Founder and CEO, Great Place to Study, Delhi Redefine the true objective of the education system The real aim of education is to ensure students use their knowledge, talent, and skills to sustain themselves and work towards th

      3 min read

    • Article

      Jobs Of The Future

      Jan 26, 2023

      Jobs Of The Future
      5 min read

    • Article

      Build Value and Drive Sales

      Mar 31, 2020

      Build Value and Drive Sales
      3 min read

    • Article

      More Than The Numbers

      Jun 24, 2018

      More Than The Numbers
      5 min read

    • Article

      Live, Learn, Leap

      Jun 3, 2019

      Live, Learn, Leap
      10 min read

    • Article

      Q&A

      May 1, 2023

      Describe the capability that companies like Netflix, UPS, Amazon and Caesars Entertainment have in common. These are all leading firms in their industries with respect to leveraging analytics as a source of competitive advantage. We now have so much

      7 min read

    • Article

      The Roots of Entrepreneurial Success

      Jan 1, 2020

      You have been involved in start-ups for many years as a founder, co-founder and Chief Technology Officer. In your experience, does entrepreneurship demand specific traits, or can anyone with a great idea do it? I don’t subscribe to the idea that it t

      6 min read

    Related categories

    Reviews for Excel BI and Dashboards in 7 Days

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Excel BI and Dashboards in 7 Days - Jared Poli

      C

      HAPTER

      1

      Getting Started with Data Management Techniques

      Introduction

      In this chapter, we cover enhancing your data, adding important calculated information, and cleaning up what we start with. On its own, inputting data is not enough. Getting the data clean, concise, and relevant will be the foundation for building our final product. We will discuss how to decide what needs to be done and consider how the work will shape our development.

      After addressing the data, structure, formatting, and any columns we need to add, we will look at power pivot for handling table manipulations that are difficult to cover with conventional methods or things that we could only achieve with Power Pivot. We will also cover more advanced formulas to give the reader a comprehensive understanding, such as VLOOKUP vs. INDEX MATCH, when to use them, and explain why we chose this over working in Power Pivot.

      We will assume that at this stage in your work, you would have a task in mind or a business problem to solve and have your data available. You will have engaged with your stakeholders to understand what they are looking for from your dashboard. These conversations are essential when dashboarding, as we must ensure that what we build is fit for purpose. In some cases, you may be the subject matter expert on the data, but it is always worth getting the opinions of your dashboard users to check what they consider essential to be successful in their roles. Maintaining alignment with the objectives of your stakeholders will not only improve the reception of your product but also speed up the development process at each level because you will know more clearly why and for what purposes you are making decisions and improving your data.

      Structure

      In this chapter, we will cover the following topics:

      Tables and ranges

      Basic formula to enhance our data

      Advanced lookup formula to enhance our data

      Cleaning the data with tools

      Saving time with keyboard shortcuts

      Power Query for advanced data table manipulation

      Freezing panes for easier viewing

      Data formats

      Understanding copy-paste

      Ensuring data complies with requirements

      Objectives

      By the end of this chapter, you will be equipped with the core techniques to manipulate, transform, and restructure your data in Excel. You will understand how to use simple and more advanced formulas to add additional columns to your data, either through calculation or reference from other data tables.

      You will consider and understand the purpose of our actions in this chapter and how they will support us with the work in the following chapters when building a dashboard.

      Tables and ranges

      The first thing you would be greeted with when opening a data spreadsheet is data; however, how Excel stores information on the sheet can affect our work.

      Traditionally, when you start with a blank worksheet to input some data, you work with the cells, rows, and columns as they are. Once you have completed your data input, we will refer to this data as a range. A range of data does not have any particular implications in Excel. It is how we refer to the table size on the spreadsheet. For example, see Figure 1.1:

      Figure 1.1: A range of data

      In this example, our range of data spans from cell A1 to cell G25. The other way that Excel can store our data for us is as a table. When dealing with a table, Excel takes care of naming, referencing, formatting, and many other helpful aspects that we might have to complete when working with a range manually.

      In some cases, you would use both; a range is easy to work with, and Excel will make no assumptions about our intentions. We have complete control and work with the sheet on a cell-by-cell basis. In contrast, a table allows Excel to understand the size of our data, and it will make assumptions as we work out our intent. An example of this would be adding a new column of data. When we add the column, the size of the table will change. If we were to type a formula into the first cell of the new column, Excel would automatically populate the rest of the columns with the equivalent formula for each row. You can see how useful this can be, and it can save us lots of needless clicks, as we would have to do with a range. The main benefit of a range is its simplicity, which is precisely what you see on the spreadsheet using Row/Column referencing.

      Now that we have discussed the differences, we will cover several ways to convert a range to a table. You must select your range by highlighting all cells in your data, then navigate to the Insert ribbon. Click the Table button (Figure 1.2). In the pop-up, you will see the selected range displayed and an option for headers:

      Figure 1.2: The table button

      Clicking OK here will complete the setup (Figure 1.3):

      Figure 1.3: The table pop-up dialogue

      Other methods involve clicking the table button before selecting the cells and selecting your range after the pop-up appears. If we click one cell in our data, Excel will often assume the range of our data for us when clicking the table button. If you create a table like this, ensure that the range in the pop-up is what you expect. Finally, instead of clicking the table button, you can use the Windows keyboard shortcut Ctrl + T to convert a range to a table quickly. Remember that a table must have a title in the first row of each column (Excel will fill these generically if none are available). These should be meaningful to the content of each column to aid with your analysis.

      If you want to convert your table back to a range, right-click anywhere in your data table, then select Table | Convert to the range. This will remove all the table referencing, but the range of data will persist in the style formatting of the table.

      A final note on tables: Excel will format them, removing them from a classic blank spreadsheet's white and grey pattern. The default will be blue, with the rows being banded. You can change the theme or revert to the classic design using the table styles under the Table Design ribbon, which is visible when selecting any cell within your table (Figure 1.4).

      It should be noted that any data created by Power Query will always be returned as a table, and it is usually advisable to leave it as such, which will be discussed in detail further on:

      Figure 1.4: Table design; see the top left option for unformatted style

      Basic formula to enhance our data

      Now that we understand how our data sits on the sheet, we can start by enhancing our data, using basic formulas to add additional columns. We will be working through a sales report example throughout this book.

      In this first example, we have two columns OrderDate and DeliveryDate. A useful column may be DaystoDelivery, which we can use to measure our order fulfillment speed.

      The first thing will be deciding where your data will sit, either next to the dependent columns or at the end of your data. It generally makes sense to keep similar columns of information grouped, and it can be useful to put new columns in place near where the columns dependent for those calculations are. However, sometimes placing them at the end will be more suitable. One example would be copying and pasting data into the sheet from another source and adding additional columns. In this case, a column in the middle will interfere with the correct placement of your data when pasting. This method is generally ineffective, and good use of Power Query can help streamline copy-paste data activities, which we will cover further on.

      To add our column, right-click the column header to the right of the column we want our data to sit next to and select Insert. A new column will be created (Figure 1.5). The new column will be empty if we consider our data a range. However, we are working with a table, and the column will create an alias, usually column 1. All columns in tables must have names, and Excel will enforce this. Type your column name at the topmost cell of your column:

      Figure 1.5: Adding a new column after DeliveryDate

      Now that we have the space for our new data, we can write a formula to create it. Excel has many date formulas, but simple date maths is simple. Given two dates, simply subtracting one from the other will produce the difference in days. No more advanced formula is needed than that. In the first cell, this will read =G2-E2, where G2 is the delivery date for the row we input the formula, and E2 is the order date (Figure 1.6):

      Figure 1.6: Basic formula for subtraction of dates

      Once we have typed our formula, clicking the Enter key will confirm our formula. Similarly, you can click the green tick icon on the left of the formula box.

      If working with a range, we must copy our formula to the rest of our rows rather than having to type in every cell. There are multiple ways that we can achieve this:

      Option 1

      Copy the cell (right-click and copy or click Ctrl + C).

      Select all the cells you want to have the formula in.

      Paste (right-click and paste or click Ctrl + V).

      Option 2

      Click the cell, and you will notice a small black rectangle (called the fill handle) in the bottom right corner of the cell.

      Click and drag this down to the bottom of your data.

      Option 3

      Click on the cell and double-click the left mouse button on the small black rectangle.

      The formula will autofill down to the end of your data, providing that your data has no significant gaps/blank ranges.

      If your data were a table, as soon as you confirm your formula, either with the Enter key or the tick, it would autofill down to the end of your dataset without further steps.

      When inputting the formula, you can type everything, but if you choose, you can also select the cells for your reference. Once you type the equals symbol in the cell, any input you enter on the spreadsheet via clicks or movement with the arrow keys will insert that selection into your formula. It is best to test this out to get used to it, but this is usually the easiest way to write the formula. Type equals, click the first date cell, type minus, then select the other. Using this method saves having to work out which row/column you are trying to calculate with. With ranges of data, Excel will use the cell reference (G2) but with a table, and it will use the column names to create a dynamic reference. It looks like [@DeliveryDate]. You can type in this format if you wish, and it will offer you autocomplete if you choose to type in this format, but it is often more straightforward to click the cells. Direct cell referencing (G2) or table name referencing ([@DeliveryDate] are equally effective, but the table naming method will be easier to review and understand when rereading your formula.

      By this point, you will have a new column of data with a formula calculating the difference in days between the order and delivery dates.

      Now that we understand how to enter formulas, we will briefly cover some valuable formulas to know what they do and how to apply them.

      We have already used basic math with a minus sum. The same can be done for addition, division, and multiplication using the appropriate keyboard symbols (+, /, *, respectively).

      We often get data of a text type, which may be poorly formatted. Take the example shown in Figure 1.7. Using the Lower, Upper, and Proper formulas, we can quickly convert the case to the appropriate style:

      Figure 1.7: Text case formula

      Sometimes, we may want to join some text, and there are two ways to handle this. You can use the & symbol to join cells of input or use the CONCAT function to combine cells. A cell or text could be used as an argument but must be entered in quotes (Figure 1.8):

      Figure 1.8: Examples of text concatenation with formula and results

      Another essential function to know is the IF function. Logically, if this is true, then do this; otherwise, do that. An example in our dataset where we may use this is to add a less granular sales channel column. Our existing ones are Online, In-Store, Distributor, and wholesaler. We may only care whether the sale is Online or Not Online so that we can calculate a simple % of goods sold online.

      To calculate this, add a new column in the desired location to the right of the

      Enjoying the preview?
      Page 1 of 1