{"id":10281,"date":"2022-12-07T10:00:26","date_gmt":"2022-12-07T04:30:26","guid":{"rendered":"https:\/\/gtm360.com\/blog\/?p=10281"},"modified":"2022-12-06T20:55:46","modified_gmt":"2022-12-06T15:25:46","slug":"excel-number-line-for-normies","status":"publish","type":"post","link":"https:\/\/gtm360.com\/blog\/2022\/12\/07\/excel-number-line-for-normies\/","title":{"rendered":"Excel Number Line For Normies"},"content":{"rendered":"

In Global Pricing Tracks PPP, Not PCI<\/strong><\/a>, we took the examples of three mainstream products and showed that multicountry prices track Purchasing Power Parity (PPP), not Per Capita Income (PCI).<\/p>\n

I’d previously used the following line diagram to illustrate this point visually:<\/p>\n

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

As you can see, in this chart, PPP and PCI are depicted as Lower Control Line<\/em> and Upper Control Line<\/em> respectively. I realized later that this representation is typically used when the variables change with time. That’s not the case in our situation where we’re capturing a snapshot of the prices of the three products at one point in time<\/em>.<\/p>\n

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

I felt that a number line – like the one shown in the exhibit on the right – would be a better way to convey that the three prices are closer to PPP than PCI, which is the crux of my argument.<\/p>\n

While it’s very easy to draw a number line by hand, I found out that it’s not a standard chart type in Microsoft Excel<\/em>.\u00a0 I then tried to figure out if it could be somehow plotted in Excel.<\/p>\n

When I googled “Number Line in Excel”, I got only one result, which was on Stack Exchange<\/a><\/em>.<\/p>\n

While the article confirmed that it’s possible to plot a number line in Excel, the instructions given in it were very cryptic. It took me a while to decipher them and a lot of trial-and-error to actually plot the number line.\u00a0 The chart you see above is the result.<\/p>\n

I thought I’ll describe the procedure in the form of a “moron-proof” step-by-step guide<\/em>.<\/p>\n

Welcome to Excel Number Line for Normies<\/strong><\/em>, the second guide in my “For Normies” series, which began with my book WordPress Migration For Normies<\/a>.<\/p>\n

\n

EXCEL NUMBER LINE FOR NORMIES<\/strong><\/p>\n

Enter the Data<\/strong><\/p>\n

    \n
  1. Let’s start with a clean slate and enter the following data in cells A1…E2.<\/li>\n<\/ol>\n

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

      \n
    1. Create a dummy Y-axis by entering zeroes in cells A3…E3.<\/li>\n<\/ol>\n

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

      Plot the Basic Chart<\/strong><\/p>\n

        \n
      1. Select cells A2…E3 > click Insert<\/em> > Charts<\/em> > Scatter<\/em> > Click the first option.<\/li>\n
      2. Select and delete the Series1<\/strong> box in the resulting chart.<\/li>\n
      3. Click the chart > click Layout<\/em> > Axis<\/em> > Primary Vertical Axis<\/em> > None<\/em>.<\/li>\n
      4. Click the chart > click Layout<\/em> > Gridlines<\/em>\u00a0> Primary Horizontal Gridlines <\/em>> None<\/em>.<\/li>\n
      5. Click the top handle of the chart and pull it down to reduce the height of the chart.<\/li>\n
      6. Click the chart > click\u00a0Layout > Chart Title > Centered Overlay Title >\u00a0<\/em>Enter Number Line<\/strong>.<\/li>\n
      7. (Optional)<\/strong> Click the chart > Click Layout<\/em> > Axis<\/em> > Primary Horizontal Axis<\/em> > More Primary Horizontal Axis Options > Number > Decimal Places = <\/em>0<\/strong>.<\/li>\n<\/ol>\n

        Enter Data Labels<\/strong><\/p>\n

          \n
        1. Click the chart > Right click > Click Data > <\/em>Series1 > Edit > <\/em>Series Name ><\/em> Click cell A1<\/strong>,\u00a0Series X Values >\u00a0<\/em>Click cell A2<\/strong>,\u00a0Series Y Values > <\/em>Click cell\u00a0A3<\/strong>. (At this point, you’ll find that four out of your five data points have vanished. Don’t worry, they will come back after you carry out the remaining steps in this section.)<\/em><\/li>\n
        2. Click the chart > Right click > Click Data > <\/em>Add > <\/em>Series Name ><\/em> Click cell B1<\/strong>,\u00a0Series X Values > <\/em>Click cell B2<\/strong>,\u00a0Series Y Values > <\/em>Click cell B3<\/strong>.<\/li>\n
        3. Repeat above step thrice to define the remaining data labels in cells C1, D1, and E1 as Series Names.<\/li>\n
        4. Click the first marker (MIN<\/strong>) in the chart > Click Layout<\/em> > Data Labels > More Data Label Options > Label Contains<\/em> >\u00a0<\/em>Uncheck\u00a0Y-value >\u00a0<\/em>Check\u00a0Series Name ><\/em> Label Position = <\/em>Above<\/em>.<\/em><\/li>\n
        5. Repeat the above step for the last marker (MAX<\/strong>).<\/li>\n
        6. Click the second marker (LABEL1<\/strong>) in the chart > Click Layout<\/em> > Data Labels > More Data Label Options > Label Contains<\/em> >\u00a0<\/em>Uncheck\u00a0Y-value >\u00a0<\/em>Check\u00a0Series Name ><\/em> Label Position = <\/em>Above > Alignment > Text Direction > <\/em>Click the third option – Rotate all text 270 degrees.<\/em><\/li>\n
        7. Repeat the above step twice for the third (LABEL2<\/strong>) and fourth (LABEL3<\/strong>) markers in the chart.<\/li>\n<\/ol>\n

          Change Marker Shape & Size<\/strong><\/p>\n

          I didn’t like the default marker shapes and sizes. They can be changed as follows:<\/p>\n

            \n
          1. Click the first marker (MIN<\/strong>) in the chart > Right click >\u00a0Format Data Series > Marker Options > Marker Type > Built-in > <\/em>Choose the shape you want from the drop-down list > Change Size = 10<\/em>.<\/li>\n
          2. Repeat the above step for the last marker (MAX<\/strong>).<\/li>\n
          3. Click the second marker (LABEL1<\/strong>) in the chart > Right click >\u00a0Format Data Series > Marker Options > Marker Type > Built-in > <\/em>Choose the shape you want from the drop-down list > Keep Size = 7 (default value)<\/em>.<\/li>\n
          4. Repeat the above step twice for the third (LABEL2<\/strong>) and fourth (LABEL3<\/strong>) markers.<\/li>\n<\/ol>\n

            Change Marker Color<\/strong><\/p>\n

            I didn’t like the default marker colors and the fact that they were different for each marker. Here’s how to fix that:<\/p>\n

              \n
            1. Click the first marker (MIN<\/strong>) in the chart >\u00a0Format > Shape Fill > <\/em>Click the color you want from the palette.<\/li>\n
            2. Repeat the above step for the last marker (MAX<\/strong>), with the same color.<\/li>\n
            3. Repeat the above step three times for the three markers in between, but with a different color.<\/li>\n<\/ol>\n
              \n

              You should now see the following chart.<\/p>\n

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

              I selected settings such that the<\/p>\n