<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jedidiah Oladele on Medium]]></title>
        <description><![CDATA[Stories by Jedidiah Oladele on Medium]]></description>
        <link>https://medium.com/@JedidiahO?source=rss-6a40df7478fe------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*z-FNi-op3SL5BWgb4wakWA.jpeg</url>
            <title>Stories by Jedidiah Oladele on Medium</title>
            <link>https://medium.com/@JedidiahO?source=rss-6a40df7478fe------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 17:14:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@JedidiahO/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Data cleaning and visualization with MS Excel]]></title>
            <link>https://medium.com/@JedidiahO/data-cleaning-and-visualization-with-ms-excel-98f1954c361b?source=rss-6a40df7478fe------2</link>
            <guid isPermaLink="false">https://medium.com/p/98f1954c361b</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[laptop]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[excel]]></category>
            <dc:creator><![CDATA[Jedidiah Oladele]]></dc:creator>
            <pubDate>Wed, 01 Jun 2022 17:58:46 GMT</pubDate>
            <atom:updated>2022-06-01T17:58:46.320Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>It is no doubt that Microsoft Excel is a great tool that a lot of data practitioners use, it provides an easy way to run a quick analysis on your tabular data, just load up the file, do some type formatting, maybe use a flash fill to split some columns, then go straight into inserting various charts. With that being said, I was a bit curious as to how easy the task would be if the data wasn’t already well cleaned and organized so I decided to build a start to finish data analytics project using <strong>only </strong>MS Excel.</p><p>I started off by searching for a dataset that was really messy and I decided to use the <a href="https://www.kaggle.com/datasets/ionaskel/laptop-prices">Laptop Prices</a> dataset from Kaggle which contains specifications and prices for around 1300 laptop models.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cKhxIn_KrM7DunrjCjse8A.png" /><figcaption>Image of raw data</figcaption></figure><h3>Data Cleaning</h3><h4>Start</h4><p>My first steps were checking for and removing duplicates with the built-in “Remove Duplicates” tool in the Data tab, extracting the actual screen resolution (e.g. 1920 x 1080) from the ScreenResolution column using autofill, and creating a new column to determine if a laptop display was a touchscreen or not, by using the SEARCH formula to search for the word “Touchscreen” in the ScreenResolution column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/546/1*2bYpjHka227-a-PCGmUvVw.png" /><figcaption>formula to get touchscreen</figcaption></figure><h4>Extracting CPU</h4><p>Next, I went into the CPU column, aside from extracting the clockspeed I also wanted to extract the CPU type (i.e. if it was an Intel Core i5, an Intel Celeron, or an AMD E-Series e.t.c) This was straightforward for most of the CPUs as it simply means selecting all the text before the second occurrence of the whitespace, except for the Intel Core series as the values I wanted were actually in the third occurrence of the whitespace character, so I wrote a formula that did just that, extract all characters from the left down to the second occurrence of the whitespace character, except if the text contains “Intel Core” in which case, it’d use the third occurrence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1Fm3QbUPJryGNDLLpLiw1g.png" /><figcaption>formula to get CPU type</figcaption></figure><h4>Extracting HDD</h4><p>Next came <strong>the tricky part</strong>, which ironically, I thought was going to be the easiest, getting meaningful information from the Memory column. I needed 3 sets of information from this:</p><ul><li>a column that contains the HDD size or “0” if the laptop didn’t have an HDD</li><li>a column for the SSD size or “0”</li><li>a column to specify whether the laptop had only an HDD, SSD, or both.</li></ul><p>I first attempted to use flash fill which didn’t work out quite well as the values didn’t really follow any straightforward pattern, they contained a combination of different tailing words like SSD, HDD, GB, and TB. I also tried to use the add “column from example” feature in power query and though it did a better job it also made some errors.</p><p>So I jumped back into the sheet to do it manually with some formulas, to make the task easier I first got rid of the tailing ‘TB” by replacing all occurrences with “000GB”, but before that, I noticed some values had a “.0” after them, (e.g. 2.0TB), so I replaced the “.0” in them with nothing, both were done using the “find and replace” feature, which for some reason would always search the entire worksheet even when the column I wanted the search to occur in was isolated from the other columns, so I copy-pasted the memory column to a separate worksheet, performed the operation, and copy-pasted it back where it belongs.</p><p>Then I created a column with a formula that would search for “HDD” in the memory column, if it exists it’ll return the index at which it occurs, if it didn’t, it’ll return “None”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/622/1*yEZ-XVyIyaLrf4NOBXM4Lg.png" /><figcaption>HDD index formula</figcaption></figure><p>And another column right beside that would extract the actual number from the text using the MID function, starting at 7 steps before the value of the index of “HDD” if that resulted in an error it means the text didn’t contain enough characters (i.e sizes less than 1000GB), thus it’d have to go back just 6 steps, if that also resulted in an error it means the index of “HDD” wasn’t found, in which case the laptop model didn’t contain any HDD so we simply return 0, then I wrapped the whole thing round a trim function, in case of any whitespace character the formulas may have caught.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*Pv4Gb-L-PnFCAN3ZW0jwOw.png" /><figcaption>HDD size extraction formula</figcaption></figure><h4>Extracting SSD</h4><p>The same process above was used in extracting the SSD.</p><p>While at it I noticed some laptop models came with a unique memory type like “Flash Storage” and “Hybrid”. So I extracted their values and added them to the SSD column. I then did some final touches by replacing values like “500” and “508” which occurred a few times, with 512, I also replaced “1000&quot; with “1024” and “2000&quot; with “2048”.</p><p>I should mention that the memory extraction would have been a lot neatly done using regex, but as of the time of writing this article, Excel didn’t come with a built-in regex function, without dabbling in VBA.</p><p>At this point, the formulas were getting lengthy, and given the number of manual processes I had done, the data cleaning pipeline isn’t quite scalable.</p><h4>Extracting GPU and OpSys</h4><p>Next came the GPU column, for this I was interested in the company that manufactured the GPU, which was easy to extract.</p><p>I also extracted the Operating system’s type using find and replace along with the wildcard character (*)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*jBKyCmIQIguhrQoCA6HXTQ.png" /></figure><p>Here’s a glance at the final data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o5N-Ecx-4FQzMZrQRef09g.png" /></figure><h3>Making Charts</h3><p>Excel’s pivot tables make charting your data really easy and dynamic, I built a couple of bar and doughnut plots with just a few steps.</p><h4>Customizing a Line Chart</h4><p>I wanted to show the average price of laptops based on the available RAM sizes using a line chart. Using a pivot table the x-axis (containing RAM sizes) would be labeled with values directly from the table (see image below), this resulted in the axis being inconsistent, which can be quite misleading as there’s no real sense in the magnitude of the values</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sNId1byFHYbPT90LZHCVFA.png" /></figure><p>However, inserting a line chart directly from the data (not a pivot chart) would allow me to use a continuous numerical scale, so I inserted one in a separate area, disabled the x-axis entirely, and used text boxes to label the points of interest.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/772/1*ZRgs1HlwpWITDul2Yo7ZIw.png" /></figure><h4>Customizing a 100% stacked bar chart</h4><p>I also wanted to display what brand of GPU each laptop’s company used the most along with the number of GPUs they had in the dataset, for this I went with a 100% stacked bar chart, but the data labels weren’t exactly what I wanted, instead of having each individual data point labeled like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J1Fs6xY4Gn8Cl6DS_E1Lyg.png" /></figure><p>I just wanted a summation of the whole category. I did this by concatenating the sum value of each category with the category’s name, and some parenthesis and then passing that column as my chart’s labels</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nlNnOTkkoXjK894t-LWRrg.png" /></figure><h3>Conclusion</h3><p>I built several other charts and customized them using different tricks and hacks and I really couldn’t think of anything I needed to do that I couldn’t find a way around using Excel, reminds me a lot of an advice from Cole Nussbaumer’s book, Storytelling with data;</p><blockquote>“Take the time to learn your tool well so that it does not become a limiting factor…”</blockquote><p>I also took the liberty of preparing a simple dashboard from some of the data I found most interesting.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/993/1*5ioL6DwUvIOmHoZu3vI_4Q.jpeg" /></figure><p>You can get the Excel file for the entire project <a href="http://www.jedidiaholadele.com/blog/laptop-excel">here</a>.</p><p>Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=98f1954c361b" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>