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

CMD/CTRL + Arrow KeyMove to the edge of the current data region
CMD/CTRL + FFind a value
CMD/CTRL + GGo to a specific cell
These shortcuts will only work if your mouse is within the Excel widget.

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

12846160Hex Tile content Add-Ons for MapForge map-making software2/25/20193/25/20195/24/2019138usd15047$15000OrlandoFL   United States
13547406Epic Stuff Library vol 4: D&D 5E/Pathfinder adaptable. 1/13/20191/26/20193/2/20191usd1$28000ArlingtonVA   United States
20761861Our House: The Lore Driven Card Game2/8/20192/8/20193/10/2019170gbp6324$2000NottinghamEngland   United Kingdom
30059044Zombicide: 2nd Edition7/30/20199/13/201910/8/201945cad4408.29$3300VancouverBC   Canada
49752892Longest Number Ever Written1/6/20196/6/20197/6/2019250usd6341$2500Los GatosCA   United States
52444976DEAD+GONE: A Competitive Horror Game1/1/20192/15/20193/3/2019667eur50384$17000Lentate sul SevesoLombardy   Italy
61166675the oracle of heaven and hell1/2/20192/7/20192/27/2019243usd7402$5000PhoenixAZ   United States
63381877Dwarf board game1/4/20192/1/20193/1/2019144usd1816$1800Los AngelesCA   United States
92129914Traveller Fifth Edition1/7/20193/26/20195/2/20194009usd109565.5$15013ArlingtonVA   United States
96526024My Pad1/9/20192/26/20193/25/2019143usd4868.25$2200Virginia BeachVA   United States
148612159In Thoth’s Wake – Zine Quest Edition!1/10/20192/12/20193/15/20195431usd528786$40000OaklandCA   United States
154179006OVER THE HILLS 2ND EDITION – THE NAPOLEONIC WARGAMES RULES1/13/20193/22/20194/21/2019286usd5113$1250RochesterNY   United States
165906545Capone: The Business of Prohibition1/22/20193/7/20194/6/201910gbp266$250GuildfordEngland   United Kingdom
182072441The EMPATH TAROT: for Inner Healing1/26/20192/1/20192/16/20191usd1$3500SeattleWA   United States
189035699Mutants et Zombie1/26/20192/6/20193/8/201916sgd904$9000SingaporeCentral Singapore   Singapore
191795368Playing Cards: The Jedi of the Old Republic1/28/20192/18/20194/4/20191331usd157049$50000PoulsboWA   United States
194146399Azote playing cards | First edition1/28/20193/5/20194/4/2019258usd6818$3500FranklinWI   United States
229060132Monster Menagerie: Gruesome Foes1/29/20196/4/20196/28/20192378usd135083.28$50000PhiladelphiaPA   United States
255478760Hit the Streets: Defend the Block tabletop RPG1/31/20192/5/20193/14/20192eur2$1500BarcelonaCatalonia   Spain
257726009Dice Palace: Display & Storage Case for Dice & Miniatures2/2/20192/23/20193/25/2019207eur7339$7000RennesIle-de-France   France
265338582peculiarity oracle, sacred space and beautiful word cards2/2/20193/5/20193/21/20195428eur389552$30000SevilleAndalusia   Spain
272156229DoubleSix Dice: Generation Two2/4/20197/9/20197/25/2019165usd5513$3000ColumbiaSC   United States
279753888The World of the Lost Lands2/4/20193/18/20194/3/201930usd1232$1000AshlandOR   United States
283822315Super Dude Jump2/6/20192/11/20193/5/2019374usd24135$2500Little RockAR   United States
302537271Sloosh Cards2/8/20192/10/20193/12/2019310sgd20861$6800SingaporeCentral Singapore   Singapore
310730322Dice Friend – Innovative Dice Tray & Dice Bag – 2 in 12/11/20195/8/20195/31/2019291usd6234$3200RochesterNY   United States
310781283Dungeon Craft: Build your own battle maps!2/12/20192/12/20192/27/2019153eur808$80ItaliaPiedmont   Italy
328751672THE MANA POOL FOR MAGIC THE GATHERING2/16/20192/25/20193/27/2019519usd7169$1000KalamazooMI   United States
343166008RPG Pins show off your love of roleplaying games! Series 1.52/16/20192/18/20193/5/2019268usd5469$500AstoriaNY   United States
375484295Way of the samurai2/19/20193/12/20194/11/2019479gbp16854$8500ReadingEngland   United Kingdom
375970229Budzzles…It’s a bud, it’s a puzzle, it’s a Budzzle!2/19/20195/13/20196/12/2019282gbp18800$15000LondonEngland   United Kingdom
376043872FIEF: The Lords Miniatures! KS Exclusive!2/19/20192/28/20193/15/2019125usd1731$500ChicagoIL   United States
376134156Warbands of the Dark Beyond2/22/20194/23/20195/29/2019710cad46303.29$27000VancouverBC   Canada
378511446Sensory White Playing Cards By Lunzi2/24/20193/1/20194/30/20194eur4$7000ParisIle-de-France   France
379865899Tremor2/24/20193/4/20194/1/201944usd2060$1500FremontCA   United States
397149344Bellum Sacrum Card Game2/26/20193/19/20194/8/2019473usd34332.5$28000DenverCO   United States
414028537Aether Studios – Swamp of Sorrows2/27/20193/12/20193/20/2019344usd6372$1000OgdenUT   United States
433917839Dark Streets & Darker Secrets3/4/20193/12/20193/28/2019212usd17396$10000Virginia BeachVA   United States
434016760Sovereign Skies3/6/20193/21/20194/20/201980usd5815$5550KissimmeeFL   United States
446366581DOUBLEHEAD KIDS3/9/20194/1/20194/30/20191430usd40642$4200San AntonioTX   United States

 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.


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.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *