“You have been using Excel the wrong way”

Introduction

Before introducing us to the nitty-gritty of how to use Excel for Data analytics, Mr Francis said to us “You have been using Excel the wrong way” and it stuck with me for the rest of the class and even after that. The Microsoft Excel brush-up class exposed us to many things that are common and considered normal when using Excel which often makes it difficult for the data stored in Excel to be used for anything meaningful. In this article, I will expose some of the things I learned in the class, and hopefully show you a better way of using Excel in the process.

Essential Excel Shortcuts and Tricks

The following are some of the Excel shortcuts and tricks that can significantly boost your Excel efficiency, whether you’re managing finances, analyzing data, or just creating spreadsheets.

1. Ctrl + Arrow Keys: Quickly navigate your data with Ctrl key shortcuts. Ctrl + Up/Down/Left/Right Arrow takes you to the edge of your data in any direction.

2. Ctrl + Shift + Arrow Keys: Select data efficiently. Ctrl + Shift + Up/Down/Left/Right Arrow selects cells in the desired direction.

3. Ctrl + Spacebar: Select the entire column of the active cell. Shift + Spacebar selects the entire row.

4. Alt + Enter: Add line breaks within a cell for cleaner, more readable content.

5. F2: Edit a cell directly. No need to double-click.

6. Ctrl + D (Fill Down) and Ctrl + R (Fill Right): Copy content from the cell above (or the cell on the left) into the selected cells.

8. Ctrl + Page Up/Page Down: Navigate between worksheets in a workbook.

9. Text to Columns: Split text in a cell based on a delimiter, like a comma.

10. Conditional Formatting: Highlight cells based on conditions. Make data visual!

Desktop App vs Web App

If you are like most people, you are already very familiar with the Excel desktop app. Excel also has a web version that works on any modern web browser like Google Chrome. This is very handy for anyone who needs to get work done on the go from anywhere. If your file is saved on One Drive, you can access it from anywhere using the Excel Desktop app or the Web app.

The downside of the Web app is that not all features are supported. Most advanced Excel features are not available on the web application.

Using Excel for Data Analytics

From the lecture, I learned that to use Excel for data analytics, one must follow the rules of the datasheet. Applying these rules helps to ensure that the datasheet is clean and devoid of obstructions that could impede data analysis.

The Rules for the Datasheet

• No empty rows and columns
• One row of header
• No Sub-total or Total
• All variables or categories should be on a Single-column
• All Dates should be on a single column
• No obstructions around the data (use comments)
• Every Row must have a unique identifier
• Each column must contain one piece of data (Eg. First name and last name in separate columns, etc)
• Always use the standard method of presenting date (Year-month-date, Y-M-D)
• Avoid merging cells
• Avoid hiding rows, columns, cells, etc.
• Always Label your sheets
• Avoid using excess capitalisation
• Colours: Apply colours professionally

Using Tables

After cleaning and transforming the datasheet using the rules above, the datasheet must be turned into an Excel table to make it easier to carry out automated operations on the data using Excel.

Analysing Data

Categories of Data

Qualitative: Qualitative are descriptions, and you can only count them

Quantitative: Quantitative data is data that you can apply arithmetic operations on. Quantitative data can be Discrete or Continuous (Continuous Data are usually decimal numbers).

Using Pivot tables

Pivot tables are essential for quick and dynamic data analysis. I learnt how easy it is to create them from clean data and how effective the tool is in facilitating data analysis. Pivot tables allow you to do all kinds of things with your data.

Other essential things to note

• Keep a separate datasheet, analysis sheet and presentation sheet.
• Keep your audience in mind when preparing the presentation. This will help you determine the most appropriate way to present the data.