Data Analysis in Excel
Companies and institutions around the world use Excel’s powerful functions to efficiently transform mountains of raw data into clear insights, and now you can too.
Table of Contents
- Microsoft Excel and Data Analysis Process
- Data table formatting
- Navigating Excel
- Keyboard shortcuts
- What can Excel do?
- Data analysis steps
- Navigating the worksheet
- EXACT() function syntax
- Removing extra spaces
- SORT() function syntax
- Nested formulas
- FILTER() function syntax
- You can use this table to practice
- Data types in Excel
- Excel’s preset alignment
- Converting to text
- TEXT() function
- Converting to numbers/VALUE() function syntax
- ROUND() with VALUE()
There’s a lot more to Microsoft Excel than SUM and COUNT. Companies and institutions around the world use Excel’s powerful functions to efficiently transform mountains of raw data into clear insights, and now you can too.
In this post, you’ll learn how to use time-saving keyboard shortcuts, convert and clean data types including text, times and dates, and build impressive logic functions and conditional aggregations.
Microsoft Excel and Data Analysis Process
Microsoft Excel is a powerful tool for all things data related. Before we start looking at the specifics within Excel, let’s talk about the Data Analysis Process. This is a handy framework that you can follow regardless of the data tool you choose.
Create a business problem – sometimes this is given to you, but it helps to understand what you want to accomplish with the analysis.
Get the data – once you know what you’re trying to solve, you have to get the appropriate data. It could be a stand alone dataset or it could include connecting to a database. It’s easy to store multiple datasets within one Excel workbook.
Explore the data – Once you have the data, you need to get familiar with it. We will review some easy steps to do that in Excel.
Prepare the data – Now that you know what the dataset includes, it will likely need some cleaning. If you speak to most anyone working with data, this is considered one of the most tedious, but necessary steps. The time spent in properly cleaning data will set you up for a much more efficient analysis later on.
Analyze the data – This is the fun part of working with data. Here is where you work to answer the business problem.
Present the findings – Once you complete the analysis, you are ready to share your findings. Usually, this includes data visualizations. It can take many forms: a simple report via email with a summary of findings with a chart or two, a real-time dashboard, or an elaborate presentation such as an annual report.
Data table formatting
Let’s cover a few tips that will help you navigate in Excel more easily, especially when you have large datasets with many rows and columns. As you start looking at the dataset there’s some fancy formatting. The alternating row colors and bold header denotes this as a data table. To make managing and analyzing a group of related data easier, you can turn a range of cells into a data table. One of the great features of data tables is the ability to auto-fill formulas in a column.
Navigating Excel
Use the arrow keys with the Control or Command button(for PC’s or Mac’s respectively) and you will jump to the next blank cell in that direction. If you have a lot of blank cells, be mindful you might not be at the end of the dataset. Page up, down, left and right keys are good alternatives to jumping blocks of rows or columns.
Keyboard shortcuts
The last two keyboards shortcuts to cover are Control or Command F and Control or Command G. They do similar, but different things. If you need to go to a specific cell, use Control or Command G and enter in the cell label you want to go to. In contrast, if you have a value you’re looking for use Control or Command F. Please note depending on the version of Excel you’re using the pop up box for these shortcuts could appear in different locations.
What can Excel do?
Excel is a versatile tool that can be used for many business activities, classwork, and even personal data organization. Which means, you can use it for storing data, calculations, data analysis and data visualizations.
Data analysis steps
Most businesses have access to an abundance of data. The challenge is to draw the right conclusions. The Data Analysis Process is a framework that can help you transform raw data into information that is useful for decision-making by users.
Here is the correct order of the steps for the Data Analysis Process:
- Understand the business problem
- Get the data
- Explore the data
- Prepare the data
- Analyze the data
- Present the findings
Navigating the worksheet
| Shortcut | Description |
|---|---|
CMD/CTRL + Arrow Key | Move to the edge of the current data region |
CMD/CTRL + F | Find a value |
CMD/CTRL + G | Go to a specific cell |
EXACT() function syntax
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order. The EXACT function is quite simple. It starts with an equal sign (this tells Excel you want to use a formula). Then type EXACT and open parentheses. Then you enter the first value or cell you want to compare followed by a comma and then the second cell. Cells are labeled by using letters for the column and numbers for rows. You can add more than two cells to compare but keep in mind it only returns TRUE or FALSE. If you have more than two cells it won’t tell you where they don’t match.
Removing extra spaces
You can use the TRIM function to get rid of those. The structure for the TRIM function is very straightforward. Type equals, TRIM, open parentheses then select the cell you want to modify and close parentheses. The function TRIM is extremely useful because it removes any extra spaces at the beginning and end of the cell value. Please note it won’t change any spaces within the cell content.
SORT() function syntax
To use the function type equals, SORT, open parentheses (are you noticing a trend?) then this function is a little different because you have to give a range of cells you want to sort. In this case, B2 to B104, using a colon to separate the start and end cells. In order to specify the ordering you need to add two optional arguments. First is the sort_index or column number you want to sort.
Nested formulas
In order to combine functions we will need to create a nested formula. This means we will be using a function as part of another function. An important note to remember when working with nested formulas is that Excel executes them from the inside out. In this example FILTER will be executed first, and then those results are used as an argument in SORT.
FILTER() function syntax
The FILTER syntax starts with equal, FILTER, open parentheses, the range to filter, then the criteria for what to include, close parentheses.
You can use this table to practice
| id | name | created_at | launched_at | deadline | exact_match | backers_count | currency | pledged | goal_USD | city | state | country | source_url | staff_pick | status |
| 12846160 | Hex Tile content Add-Ons for MapForge map-making software | 2/25/2019 | 3/25/2019 | 5/24/2019 | 138 | usd | 15047 | $15000 | Orlando | FL | United States | https://www.kickstarter.com/discover/categories/games/live%20games | FALSE | successful | |
| 13547406 | Epic Stuff Library vol 4: D&D 5E/Pathfinder adaptable. | 1/13/2019 | 1/26/2019 | 3/2/2019 | 1 | usd | 1 | $28000 | Arlington | VA | United States | https://www.kickstarter.com/discover/categories/games/mobile%20games | FALSE | failed | |
| 20761861 | Our House: The Lore Driven Card Game | 2/8/2019 | 2/8/2019 | 3/10/2019 | 170 | gbp | 6324 | $2000 | Nottingham | England | United Kingdom | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 30059044 | Zombicide: 2nd Edition | 7/30/2019 | 9/13/2019 | 10/8/2019 | 45 | cad | 4408.29 | $3300 | Vancouver | BC | Canada | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 49752892 | Longest Number Ever Written | 1/6/2019 | 6/6/2019 | 7/6/2019 | 250 | usd | 6341 | $2500 | Los Gatos | CA | United States | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 52444976 | DEAD+GONE: A Competitive Horror Game | 1/1/2019 | 2/15/2019 | 3/3/2019 | 667 | eur | 50384 | $17000 | Lentate sul Seveso | Lombardy | Italy | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 61166675 | the oracle of heaven and hell | 1/2/2019 | 2/7/2019 | 2/27/2019 | 243 | usd | 7402 | $5000 | Phoenix | AZ | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 63381877 | Dwarf board game | 1/4/2019 | 2/1/2019 | 3/1/2019 | 144 | usd | 1816 | $1800 | Los Angeles | CA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | TRUE | successful | |
| 92129914 | Traveller Fifth Edition | 1/7/2019 | 3/26/2019 | 5/2/2019 | 4009 | usd | 109565.5 | $15013 | Arlington | VA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 96526024 | My Pad | 1/9/2019 | 2/26/2019 | 3/25/2019 | 143 | usd | 4868.25 | $2200 | Virginia Beach | VA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 148612159 | In Thoth’s Wake – Zine Quest Edition! | 1/10/2019 | 2/12/2019 | 3/15/2019 | 5431 | usd | 528786 | $40000 | Oakland | CA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 154179006 | OVER THE HILLS 2ND EDITION – THE NAPOLEONIC WARGAMES RULES | 1/13/2019 | 3/22/2019 | 4/21/2019 | 286 | usd | 5113 | $1250 | Rochester | NY | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 165906545 | Capone: The Business of Prohibition | 1/22/2019 | 3/7/2019 | 4/6/2019 | 10 | gbp | 266 | $250 | Guildford | England | United Kingdom | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 182072441 | The EMPATH TAROT: for Inner Healing | 1/26/2019 | 2/1/2019 | 2/16/2019 | 1 | usd | 1 | $3500 | Seattle | WA | United States | https://www.kickstarter.com/discover/categories/games/live%20games | FALSE | failed | |
| 189035699 | Mutants et Zombie | 1/26/2019 | 2/6/2019 | 3/8/2019 | 16 | sgd | 904 | $9000 | Singapore | Central Singapore | Singapore | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | failed | |
| 191795368 | Playing Cards: The Jedi of the Old Republic | 1/28/2019 | 2/18/2019 | 4/4/2019 | 1331 | usd | 157049 | $50000 | Poulsbo | WA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 194146399 | Azote playing cards | First edition | 1/28/2019 | 3/5/2019 | 4/4/2019 | 258 | usd | 6818 | $3500 | Franklin | WI | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 229060132 | Monster Menagerie: Gruesome Foes | 1/29/2019 | 6/4/2019 | 6/28/2019 | 2378 | usd | 135083.28 | $50000 | Philadelphia | PA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | TRUE | successful | |
| 255478760 | Hit the Streets: Defend the Block tabletop RPG | 1/31/2019 | 2/5/2019 | 3/14/2019 | 2 | eur | 2 | $1500 | Barcelona | Catalonia | Spain | https://www.kickstarter.com/discover/categories/games/live%20games | FALSE | failed | |
| 257726009 | Dice Palace: Display & Storage Case for Dice & Miniatures | 2/2/2019 | 2/23/2019 | 3/25/2019 | 207 | eur | 7339 | $7000 | Rennes | Ile-de-France | France | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 265338582 | peculiarity oracle, sacred space and beautiful word cards | 2/2/2019 | 3/5/2019 | 3/21/2019 | 5428 | eur | 389552 | $30000 | Seville | Andalusia | Spain | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 272156229 | DoubleSix Dice: Generation Two | 2/4/2019 | 7/9/2019 | 7/25/2019 | 165 | usd | 5513 | $3000 | Columbia | SC | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 279753888 | The World of the Lost Lands | 2/4/2019 | 3/18/2019 | 4/3/2019 | 30 | usd | 1232 | $1000 | Ashland | OR | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 283822315 | Super Dude Jump | 2/6/2019 | 2/11/2019 | 3/5/2019 | 374 | usd | 24135 | $2500 | Little Rock | AR | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 302537271 | Sloosh Cards | 2/8/2019 | 2/10/2019 | 3/12/2019 | 310 | sgd | 20861 | $6800 | Singapore | Central Singapore | Singapore | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 310730322 | Dice Friend – Innovative Dice Tray & Dice Bag – 2 in 1 | 2/11/2019 | 5/8/2019 | 5/31/2019 | 291 | usd | 6234 | $3200 | Rochester | NY | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 310781283 | Dungeon Craft: Build your own battle maps! | 2/12/2019 | 2/12/2019 | 2/27/2019 | 153 | eur | 808 | $80 | Italia | Piedmont | Italy | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 328751672 | THE MANA POOL FOR MAGIC THE GATHERING | 2/16/2019 | 2/25/2019 | 3/27/2019 | 519 | usd | 7169 | $1000 | Kalamazoo | MI | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 343166008 | RPG Pins show off your love of roleplaying games! Series 1.5 | 2/16/2019 | 2/18/2019 | 3/5/2019 | 268 | usd | 5469 | $500 | Astoria | NY | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | TRUE | successful | |
| 375484295 | Way of the samurai | 2/19/2019 | 3/12/2019 | 4/11/2019 | 479 | gbp | 16854 | $8500 | Reading | England | United Kingdom | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 375970229 | Budzzles…It’s a bud, it’s a puzzle, it’s a Budzzle! | 2/19/2019 | 5/13/2019 | 6/12/2019 | 282 | gbp | 18800 | $15000 | London | England | United Kingdom | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 376043872 | FIEF: The Lords Miniatures! KS Exclusive! | 2/19/2019 | 2/28/2019 | 3/15/2019 | 125 | usd | 1731 | $500 | Chicago | IL | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 376134156 | Warbands of the Dark Beyond | 2/22/2019 | 4/23/2019 | 5/29/2019 | 710 | cad | 46303.29 | $27000 | Vancouver | BC | Canada | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 378511446 | Sensory White Playing Cards By Lunzi | 2/24/2019 | 3/1/2019 | 4/30/2019 | 4 | eur | 4 | $7000 | Paris | Ile-de-France | France | https://www.kickstarter.com/discover/categories/games/mobile%20games | FALSE | failed | |
| 379865899 | Tremor | 2/24/2019 | 3/4/2019 | 4/1/2019 | 44 | usd | 2060 | $1500 | Fremont | CA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 397149344 | Bellum Sacrum Card Game | 2/26/2019 | 3/19/2019 | 4/8/2019 | 473 | usd | 34332.5 | $28000 | Denver | CO | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 414028537 | Aether Studios – Swamp of Sorrows | 2/27/2019 | 3/12/2019 | 3/20/2019 | 344 | usd | 6372 | $1000 | Ogden | UT | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 433917839 | Dark Streets & Darker Secrets | 3/4/2019 | 3/12/2019 | 3/28/2019 | 212 | usd | 17396 | $10000 | Virginia Beach | VA | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | FALSE | successful | |
| 434016760 | Sovereign Skies | 3/6/2019 | 3/21/2019 | 4/20/2019 | 80 | usd | 5815 | $5550 | Kissimmee | FL | United States | https://www.kickstarter.com/discover/categories/games/playing%20cards | FALSE | successful | |
| 446366581 | DOUBLEHEAD KIDS | 3/9/2019 | 4/1/2019 | 4/30/2019 | 1430 | usd | 40642 | $4200 | San Antonio | TX | United States | https://www.kickstarter.com/discover/categories/games/tabletop%20games | TRUE | successful |
Data types in Excel
In Excel there are four primary data types: text, can include strings of characters or numbers that will be treated as text. You cannot do math functions on text fields. numeric, all number formats including integer, decimals, percentages, etc. date, includes both date and times and boolean, these are logic values of TRUE or False Often times, Excel will apply a general data type label but in order to make sure formulas behave properly, it’s good practice to assign the correct specific data type.
Excel’s preset alignment
Quick tip, if you see values left aligned then Excel is treating it as text, centered is logical and right aligned is numeric or datetime.
Converting to text
Sometimes a column may look like a numeric value but in reality, it shouldn’t be treated as such. For example, if we look at the id column, we see that it is right aligned and is considered a number. Since we won’t need to perform any arithmetic (sum, average, etc.) on the column we are going to change the data type to text so it behaves like a string.
1 https://support.office.com/en—us/excel
TEXT() function
The function is TEXT and the syntax is pretty simple: equals, TEXT, open parentheses, then the cell you want to reassign, the next argument specifies the desired format which in this case is 9 zeros wrapped in double quotation marks, close parentheses. The 9 zeros represent the minimum number of characters in the id field. There is a large list of format options that you find on the Excel online help center for more details. Notice the column alignment shifted from the left to the right.
1 https://support.office.com/en—us/article/TEXT—function—20D5AC4D—7B94—49FD—BB38—93D29371225C
Converting to numbers/VALUE() function syntax
The function in the case is VALUE which converts a text string that represents a number to a number. The syntax is again very straightforward: equals, VALUE, open parentheses, then the cell you want to reassign, close parentheses. Now see how the dollar signs are removed and the values have shifted to be right aligned.
ROUND() with VALUE()
We’re going to wrap the ROUND function around the VALUE formula we just made. The syntax starts with equal, ROUND, open parentheses, then VALUE, open parentheses, the cell you want to adjust, close parentheses, then the number of decimal places to include, in this case, we want zero decimal places, then close parentheses again.
It is time to practice. Copy the table above into an Excel document and use the functions you’ve learned.