Analyze data in Excel with a case study of Dry Port to Dry Port (Part 1)

Published by Ngoc Tran on 27 September 202127 September 2021

Is your goal to improve your data analysis skill in Excel? If yes, this blog is going to assist you on how to analyze a set of data in Excel step by step. Requirements are that you stay focused and have basic knowledge of Excel. Some tips to keep in mind:

• Always backup all versions of your Excel file (via cloud storage, USB, etc.)
• Apply font and font size consistently throughout your Excel
• Ensure all rows and columns are unhide

#1: Define a problem and set KPIs

Determine your main problem in your business/study/work is firstly crucial, because that is where you design your action plan and set your KPIs. For example, an increase of 10% defects of your bikes is the primary issue and you aim to reduce it by 5%. In order to make changes, you simply look at all the data you have and brainstorm on how to unfold the meaning behind it. This blog provides you an example of a dataset from an export flow via a Dry Port to Dry Port (DP2DP) supply chain.

Click on the link and use the spreadsheet as your workspace for the next steps.

*Please note that the data set is made up for the purpose of guidance in this blog. The idea is inspired from project “Smart Supply Chain Fashion & Lifestyle” of KennisDC Logistiek. For more information, please refer to https://www.kennisdclogistiek.nl/projecten/smart-supply-chain-fashion-en-lifestyle

#2: Data gathering and cleaning

Before collecting data, a fundamental rule that worth being considered is to design a list of needed data in a spreadsheet like Excel, and test your plan with some data made up by yourself (applied for real-time data). Doing this gives you a big advantage to see if your plan actually works properly.

During data collection, you can either do it manually into your Excel, or extract data from the sources into Excel. For the latter method, you can:

• Directly extract data from Excel: Go to tab Data => Click “From Web” => Follow Excel instruction
• In case the aforementioned technique does not work, search data extraction software with key words such as “data extraction software”, “get external data to Excel”, etc.

Following these methods already allows you to clean your data as you will be asked to choose which data you want. If you gather data manually, you should stick to the fundamental rule, thus having an idea on which data is needed to answer the main question. Besides that, some Excel tips can be used to clean your data as follows. Try to apply it into our example spreadsheet (Only check the answer after trying)!

• Conditional formatting – change color of cells
• Idea for application: specific colors for specific products/ customers/ countries/ transport routes, etc.
• How: In tab Home, click “Conditional Formatting” => chose which options you want.
• Example: In “Conditional Formatting” => Click “New rule” => Click “Use a formula to determine which cells to format” => Fill in the rule =A1=”Rail” => Click “Format” in Preview => You can choose “Font” (color only the letters) or “Fill” (color the cells), then decide the color => Click “OK” => Click “Apply”
• Extend formula across/ down – copy a formula to another cell
• How: Choose any cell => Move your mouse at the lower corner on the right side of the cell and you see + (cross hair) => Double click it
• Format Painter – copy formatting of the chosen cell to many cells/ groups of cells
• How: Click on a cell which has the formatting you desire => Double click “Format Painter” under Home => Click on other cells that you would like to apply the formatting

Does it sound familiar to you? These tips are simple, friendly-user yet effective to use in your personal life, and your student or working life. Unless you are an Excel expert or someone who dedicated working with Excel, the Excel formulas mentioned in this post and the latter one are all what you need to know when analyzing data in Excel. The principle of using Excel is not to know all the formulas, but how creative you are in order to make use of a formula for different purposes.

Share if you have other ideas. All appreciated! Stay safe and see you in the next post of this blog!

Please don’t hesitate to share this Practical example on how to collect, clean and analyse data in Excel – Dry Port to Dry Port!

Series Excel skills