{"id":5916,"date":"2015-11-12T15:32:37","date_gmt":"2015-11-12T15:32:37","guid":{"rendered":"https:\/\/www.investintech.com\/resources\/blog\/?p=5916"},"modified":"2021-08-03T15:31:47","modified_gmt":"2021-08-03T15:31:47","slug":"excel-tips-for-data-analysts","status":"publish","type":"post","link":"https:\/\/www.investintech.com\/resources\/blog\/archives\/5916-excel-tips-for-data-analysts.html","title":{"rendered":"Top Excel Tips For Data Analysts"},"content":{"rendered":"

\"Top<\/p>\n

It\u2019s an obvious, well-known fact that data and business go hand in hand. You can\u2019t manage one without affecting the other. And whether you\u2019re analyzing a client\u2019s data or using your company\u2019s data to make executive decisions, your tools have to be able to handle the tasks you perform with that information. <\/span><\/p>\n

For instance,<\/span> if you<\/span>\u2019<\/span>re a data analyst, most of the time you go through these stages of data analysis: <\/span><\/p>\n

    \n
  1. Data Cleaning:<\/strong> Transform and rearrange the data in a way suitable for data analysis<\/span><\/li>\n
  2. Data Analysis:<\/strong> Perform the necessary calculations to extract useful information <\/span><\/li>\n
  3. Data Visualization:<\/strong> Use graphs or other type of visualization technique to show your results<\/span><\/li>\n<\/ol>\n

    While these may be impossible to handle manually, they\u2019re perfectly manageable with Microsoft Excel. The application is advanced yet user friendly enough for the average user. <\/span><\/p>\n

    However, the tricky part you probably struggle with is knowing how to access and apply the right functionalities to your data. Well, it\u2019s time to stop the struggle.<\/span><\/p>\n

    In this post<\/span>,<\/span> I\u2019ll show you some <\/span>Excel<\/span> tips you can use <\/span>at<\/span> <\/span>each of the data analysis stages.  Click through to jump to a specific section or tip.<\/span><\/p>\n

    \n
    TIPS FOR DATA CLEANING<\/strong><\/a><\/div>\n
    1) Change format of numbers from text to numeric<\/a><\/div>\n
    2) Unpivot columns in a data set (Multiple consolidation ranges and Power Query)<\/a><\/div>\n
    3) Merge data from several csv files into a single folder (RDBMerge Add-in and Power Query)<\/a><\/div>\n
    4) Fill empty spaces from content above (Ctrl + Enter trick and Power Query)<\/a><\/div>\n
    DATA ANALYSIS<\/strong><\/a><\/div>\n
    5) Create auto expandable ranges with Excel Tables (Source for pivots, dropdown lists and formulas)<\/a><\/div>\n
    6) How to do two way lookup with INDEX and MATCH<\/a><\/div>\n
    7) Creating OR criteria within SUMIF\/COUNTIF (Combination of SUMPRODUCT and SUMIF\/COUNTIF)<\/a><\/div>\n
    8) Counting unique items within PivotTables (Using the Excel Data Model)<\/a><\/div>\n
    DATA VISUALIZATION<\/strong><\/a><\/div>\n
    9) Quickly visualize trends with Sparklines<\/a><\/div>\n
    10) Create dynamic titles in charts (Use of cell references within chart objects)<\/a><\/div>\n
    11) Dealing with empty cells in charts and sparklines [use NA()]<\/a><\/div>\n
    12) Save time with Quick Analysis<\/a><\/div>\n<\/div>\n

    <\/p>\n

    Tips for Data Cleaning<\/b><\/h2>\n

    With <\/span>each of the tips for data cleaning<\/span>,<\/span> you<\/span>\u2019ll <\/span>learn <\/span>how to use a native Excel feature and how to accomplish the same goal with Power Query. Power Query is a built-in feature in Excel 2016 and an Add-in for Excel 2010\/2013. This Add-in helps you to extract, transform, and load your data with just a few clicks.<\/span><\/p>\n

    Note: Within Excel 2016, the Power Query features can be found in the <\/span>Get & Transform<\/b> group of the <\/span>Data<\/b> tab. <\/span>Use this <\/span>link<\/span><\/a> to get more information about Power Query or to download it.<\/span><\/p>\n

    For the following tips<\/span>,<\/span> I\u2019ll assume that you already have the data within Power Query.<\/span><\/p>\n

    1) Change Format Of Numbers Trom Text To Numeric<\/b><\/h3>\n

    Sometimes when you import data from an external source other than Excel, numbers are imported as text. If this is the case, Excel will alert you by showing a green tooltip in the top-left corner of the cell. If you click the tooltip you<\/span>\u2019ll <\/span>see the following message:<\/span><\/p>\n

    \"Excel<\/p>\n

    Depending on your computer and the number of values in the range<\/span>,<\/span> you can quickly convert the values to numbers by clicking on <\/span>\u2018Convert to number\u2019<\/span><\/i> within the tooltip options.<\/span><\/p>\n

    \"Convert<\/p>\n

    However, if there are  more than 1000 values, you’ll <\/span>need to wait a couple of seconds while Excel finishes the conversion.<\/span><\/p>\n

    A faster way of converting the values to number format is to use Text-to-Columns:<\/span><\/p>\n

      \n
    1. Select the range with the values to be converted.<\/span><\/li>\n
    2. Go to Data > Text to Columns.<\/span><\/li>\n
    3. Select <\/span>Delimited<\/span><\/i> and click <\/span>Next.<\/span><\/i><\/li>\n
    4. Uncheck <\/b>all the checkboxes for delimiters (<\/span>s<\/span>ee below) and click <\/span>Next.<\/span><\/i><\/li>\n<\/ol>\n

      \"Excel<\/p>\n

      5.  Then select <\/span>General<\/span><\/i> and click on <\/span>Finish.<\/span><\/i><\/p>\n

      When you have lots of numbers to convert this tip will be much faster than waiting for all the numbers to be converted.<\/span><\/p>\n

      In Power Query this is even easier, just:<\/span><\/p>\n

        \n
      1. Right click on the column header of the column you want to convert.<\/span><\/li>\n
      2. Go to <\/span>Change Type<\/span><\/i>.<\/span><\/li>\n
      3. Then select the type of number you want (Decimal, Whole Number, \u2026).<\/span><\/li>\n<\/ol>\n

        \"Excel<\/p>\n

        2)  Unpivot Columns In A Data Set (Multiple Consolidation Ranges And Power Query)<\/b><\/h3>\n

        If you’re going to create a PivotTable or<\/span> <\/span>use any statistical package<\/span>,<\/span> it<\/span>\u2019s<\/span> strongly recommended to have each variable on a single column. For example, if you’re creating a PivotTable of shipments you need to have all the shipment values in the same column. <\/span>Unfortunately, <\/span>you won\u2019t<\/span> <\/span>always<\/span> receive the data in that tidy format.<\/span><\/p>\n

        Let\u2019s say you receive the following data set:<\/span><\/p>\n

        \"Excel<\/p>\n

        Even though it looks visually appealing <\/span>it isn\u2019t<\/span>  very useful for PivotTables and other type of features. The best format for data analysis would be as follows:<\/span><\/p>\n

        \"Excel<\/span><\/p>\n

        You can accomplish this using <\/span>multiple consolidation ranges<\/span> in Excel or using <\/span>Power Query<\/span>.<\/span><\/p>\n

        a) Unpivot columns using multiple consolidation ranges (this is the large and cumbersome way<\/span><\/p>\n

        -Press Alt + D + P (this will open the legacy PivotTable dialog box, see below)<\/span><\/p>\n

        -Select <\/span>\u2018Multiple consolidation ranges\u2019<\/span><\/i> and click <\/span>Next.<\/span><\/i><\/p>\n

        \"Excel<\/p>\n

        – In the next step, select <\/span>\u2018I will create the page fields\u2019<\/span><\/i> and click <\/span>Next.<\/span><\/i><\/p>\n

        – Click <\/span>o<\/span>n the range selection button \"Select <\/span>to select the range that contains the data<\/span>. C<\/span>lick on \u20180\u2019 page fields and click on <\/span>Next<\/span><\/i>:<\/span><\/p>\n

        \"Selecting<\/p>\n

        A PivotTable will be created with the same structure of the original data source. To unpivot the data just double click on the grand total cell of the PivotTable (bottom-right corner)<\/span>:<\/span><\/p>\n

        \"Excel<\/p>\n

        Afterwards<\/span>,<\/span> the data set will be showed in a tidy format on a new worksheet:<\/span><\/p>\n

        \"Excel<\/p>\n

        You might want to add proper column names to the table, since the default names are: <\/span>Row<\/span><\/i>, <\/span>Column<\/span><\/i>, and <\/span>Value<\/span><\/i>.<\/span><\/p>\n

        b) Unpivot columns using Power Query (this is the quick and easy way)<\/span><\/p>\n

        – Select the columns you want to unpivot.<\/span><\/p>\n

        – Right click and select <\/span>Unpivot Columns.<\/span><\/i><\/p>\n

        \"Excel<\/p>\n

        Notice that if you have several columns that <\/span>won\u2019t be \u201cunpivoted\u201d, using Multiple consolidation ranges<\/span><\/i> will require additional steps. However<\/span>,<\/span> with Power Query you just need to follow exactly the same steps explained here.<\/span><\/span><\/p>\n

        3) Merge Data From Several CSV  Files In<\/b>to<\/b> A S<\/b>ingle F<\/b>older (RDBMerge Add-in And Power Query)<\/b><\/h3>\n

        Sometimes the data is stored in several csv (Comma Separated Value) files that need to be imported and merged into a single worksheet.<\/span><\/p>\n

        One possible way of doing this is <\/span>by<\/span> using RDBMerge (a free Excel Add-in) created by Ron de Bruin. This Add-in can be downloaded for free from Ron\u2019s <\/span>website<\/span><\/a> where you can also find <\/span><\/span>steps to install the Add-in.<\/span><\/p>\n

        Once you\u2019ve installed the Add-in, <\/span><\/span><\/span>follow these <\/span>steps to use it for importing and merging csv files:<\/span><\/p>\n

          \n
        1. Go to Data and click on <\/span>\u2018RDBMerge Add-in\u2019.<\/span><\/i><\/li>\n
        2. Within the dialog box, click on <\/span>Browse.<\/span><\/i><\/li>\n
        3. Browse to the folder and press <\/span>OK<\/span><\/i>. The path to the folder will appear in the top part. <\/span><\/li>\n
        4. Select CSV from the <\/span>Which files<\/span><\/i> dropdown list.<\/span><\/li>\n
        5. Select cells range you want to extract. To import the whole file, select <\/span>First cell<\/span><\/i>.<\/span><\/li>\n
        6. Click on <\/span>Merge<\/span><\/i>.<\/span><\/li>\n<\/ol>\n

          \"Selecting<\/p>\n

          To do this in Power Query, follow these steps:<\/span><\/p>\n

          1. Within the Power Query menu go to the \u2018Get External Data\u2019 group select \u2018From File\u2019 and then \u2018From Folder\u2019.<\/span><\/p>\n

          \"Importing2.  <\/span>Click on \u2018Browse\u2019 and browse <\/span>for <\/span>the folder that contains<\/span> the<\/span> files you want to import<\/span>.  <\/span>C<\/span>lick Ok. <\/span>If there are other files within the folder that are not CSV, go the columns \u2018Extension\u2019 and select \u201c.csv\u201d.<\/span><\/p>\n

          \"Selecting<\/p>\n

          3.  Click on the double down arrow button in the <\/span>Content<\/span><\/i> column. All the csv files will be appended together.<\/span><\/p>\n

          \"Expanding<\/p>\n

          If the files have header rows<\/span>,<\/span> you can exclude them from the data using the filters. In this particular example, the header row contains the word \u201cBatch\u201d in the first row of each file. The header rows can be excluded<\/span> by<\/span> unselecting the word \u201cBatch\u201d from the filter.<\/span><\/p>\n

          \"Removing<\/p>\n

          4)  Fill Empty Spaces From Content Above (Ctrl + Enter Trick And Power Query)<\/b><\/h3>\n

          If you\u2019re a data analyst, I bet you<\/span>\u2019ve <\/span>received a data set with the following format <\/span>at least once<\/span>:<\/span><\/p>\n

          \"Empty<\/p>\n

          Yes, the dreaded empty spaces\u2026 <\/span><\/p>\n

          These empty spaces cause a lot <\/span>of<\/span> problems when using PivotTables or lookup functions. Therefore, to use this data set you need to complete the empty spaces with the content above.<\/span><\/p>\n

          There are three ways of doing this:<\/span><\/p>\n

            \n
          1. Fill down the content by dragging with the mouse or doing copy\/paste (the never-ending way). Select each item and drag it to complete the empty spaces or do copy\/paste into the empty spaces<\/span><\/li>\n
          2. Fill down with \u2018Go To > Special\u2019 (The fast but complex way):<\/span><\/li>\n<\/ol>\n

            a) Select the range with the empty spaces (You can select the whole columns if it is easier).<\/p>\n

            b) Press Ctrl + G (This will show the \u201cGo – to dialog box\u201d) and click on <\/span>Special.<\/span><\/i><\/p>\n

            \"Excel<\/p>\n

            c) Click on \u201cBlanks\u201d and press OK. <\/span><\/p>\n

            d) Type \u201c=\u201d and select the cell just above the active cell.<\/p>\n

            \"Selecting<\/p>\n

            e)  Press Ctrl + Enter.<\/span><\/p>\n

            3. Fill down with Power Query (<\/span>t<\/span>he extremely fast and easy way): <\/span><\/p>\n

            a) Within Power Query select the columns where you want to fill the empty spaces.<\/span><\/p>\n

            b) Right-click on the header of any of them.<\/span><\/p>\n

            c) Go to <\/span>Fill<\/span><\/i> > <\/span>Down.<\/span><\/i><\/p>\n

            \"Selecting<\/p>\n

            As you can see, Power Query makes the data cleaning process extremely easy. Best of all, once you perform the cleaning steps, Power Query will store them and you can repeat them whenever you want for other data sets.<\/span><\/p>\n

            Tips for Data Analysis<\/b><\/h2>\n

            Now that you know some data cleaning tips, let\u2019s see some data analysis tips.<\/span><\/p>\n

            5) Create Auto Expandable Ranges With Excel Tables (Source For Pivots, Dropdown Lists And Formulas)<\/b><\/h3>\n

            One of the most underused features of MS Excel are Excel Tables. Excel Tables have wonderful properties that allow you to work more efficiently. Some of these features <\/span>are<\/span> include:<\/span><\/p>\n

              \n
            1. Formula <\/span>A<\/span>uto <\/span>F<\/span>ill<\/span>:<\/span> Once you enter a formula in a table it will be automatically <\/span>be <\/span>copied to the rest of the table.<\/span><\/li>\n
            2. Auto <\/span>E<\/span>xpansion: New items typed below or at the right of the table become part of the table.<\/span><\/li>\n
            3. Visible headers: Regardless of your position within the table, your headers will always be visible.<\/span><\/span><\/li>\n
            4. Automatic Total Row<\/span>: <\/span> <\/span>To calculate the total of a row,<\/span> you just have to select the desired formula.<\/span><\/li>\n<\/ol>\n

              In this tip<\/span>, we\u2019ll<\/span> take advantage of the auto expansion feature when using an Excel Table as a source for a PivotTable, dropdown list, VLOOKUP, etc\u2026<\/span><\/p>\n

              To create a table just select the range you want to include in the table and press Ctrl + T. <\/span>After you press Ctrl + T, the selected range will change to a format similar to the one shown below:<\/span><\/p>\n

              \"Example<\/p>\n