Excel For Data Analysis
Since 1987, Microsoft Excel has been used in virtually every office by employees with various job titles. But how is Excel used in data analysis today and can it be learned? While some enjoy playing with pivotal tables and histograms, others limit themselves to simple pie-charts and conditional formatting. We explain the pros and cons of using Excel for data analysis, the top Excel functions that every data analyst needs to know.
What is Excel?
Excel is spreadsheet software. Excel is a convenient go-to software that is both comprehensible and familiar, and a key part of Excel is how it can be used for ad hoc analysis. Many people are familiar with Excel and that level of comfort is where much of its power stems from.
How do Data Analysts use Excel?
Data analysts use Excel in much the same way that you might use the calculator app on your Phone. When you aren’t sure what is going on with a dataset, putting it into Excel can bring clarity to the project. You don’t have to be a Data Analyst by title to start using Excel, though. If you can type and hit enter, then you can start using Excel. That’s the beauty of it – you can self-study and continue to learn.
What types of data can be entered into an Excel spreadsheet?
Everyone thinks of financial data with Excel, but it can apply to any industry data. All types of data are appropriate! The only exception would be the size of the datasets. Small to medium-size datasets are best for Excel. If a dataset becomes too large, it’s cumbersome in Excel. Many times, data analysts will take a look at the underlying data using Excel before they use a heavier application like Python or SQL.
Pros & Cons of Excel in Data Analysis
Excel is powerful because it’s quick and easy to use, but the downside is that it isn’t scalable. As data sizes become larger, we hit limits in our notebook and time limits on our computer. Excel also lacks the ability to automate processes.
Alternatives to Excel
Google Sheets is a free alternative to Excel. The collaborative aspect of Google Sheets is great, but it also makes it harder to protect your data from other parts of the company. It is awesome software but Google Sheets isn’t quite as advanced as Excel is and I doubt they will ever catch up. Excel is continuously improving and expanding.
Analyzing Data Sets with Excel
To know how to analyze data in excel, you can instantly create different types of charts, including line and column charts, or add miniature graphs. You can also apply a table style, create PivotTables, quickly insert totals, and apply conditional formatting. Analyzing large data sets with Excel makes work easier if you follow a few simple rules:
Select the cells that contain the data you want to analyze.
Click the Quick Analysis button image button that appears to the bottom right of your selected data (or press CRTL + Q).
Selected data with Quick Analysis Lens button visible. In the Quick Analysis gallery, select a tab you want. For example, choose Charts to see your data in a chart.
Pick an option, or just point to each one to see a preview.
You might notice that the options you can choose are not always the same. That is often because the options change based on the type of data you have selected in your workbook.
To understand the best way to analyze data in excel, you might want to know which analysis option is suitable for you. Here we offer you a basic overview of some of the best options to choose from.
Formatting: Formatting lets you highlight parts of your data by adding things like data bars and colors. This lets you quickly see high and low values, among other things.
Charts: Charts Excel recommends different charts, based on the type of data you have selected. If you do not see the chart you want, click More Charts.
Totals: Totals let you calculate the numbers in columns and rows. For example, Running Total inserts a total that grows as you add items to your data. Click the little black arrows on the right and left to see additional options.
Tables: Tables make it easy to filter and sort your data. If you do not see the table style you want, click More.
Sparklines: Sparklines are like tiny graphs that you can show alongside your data. They provide a quick way to see trends.
How to Analyze Data in Excel: Data Analysis
Data Analysis is simpler and faster with Excel analytics. Here, we offer some tips for work:
Create auto expandable ranges with Excel tables: One of the most underused features of MS Excel is Excel Tables. Excel Tables have wonderful properties that allow you to work more efficiently. Some of these features include:
Formula Auto Fill: Once you enter a formula in a table it will be automatically be copied to the rest of the table.
Auto Expansion: New items typed below or at the right of the table become part of the table.
Visible headers: Regardless of your position within the table, your headers will always be visible.
Automatic Total Row: To calculate the total of a row, you just have to select the desired formula.
Use Excel Tables as part of a formula: Like in dropdown lists, if you have a formula that depends on a Table, when you add new items to the Table, the reference in the formula will be automatically updated.
Use Excel Tables as a source for a chart: Charts will be updated automatically as well if you use an Excel Table as a source. As you can see, Excel Tables allow you to create data sources that do not have to be updated when new data is included.
How to Analyze Data in Excel: Data Visualization
Quickly visualize trends with sparklines: Sparklines are a visualization feature of MS Excel that allows you to quickly visualize the overall trend of a set of values. Sparklines are mini-graphs located inside of cells. You may want to visualize the overall trend of monthly sales by a group of salesmen.
To create the sparklines, follow these steps below:
Select the range that contains the data that you will plot (This step is recommended but not required, you can select the data range later).
Go to Insert > Sparklines > Select the type of sparkline you want (Line, Column, or Win/Loss). For this specific example, I will choose Lines.
Click on the range selection button Select Range Excel Button to browse for the location of the sparklines, press Enter and click OK. Make sure you select a location that is proportional to the data source. For example, if the data source range contains 6 rows then the location of the sparkline must contain 6 rows.
To format the sparkline you may try the following:
To change the colour of markers:
Click on any cell within the sparkline to show the Sparkline Tools menu.
In the Sparkline tools menu, go to Marker Color and change the colour for the specific markers you want.
For example High points on the green, Low points on red, and the remaining in blue.
To change the width of the lines:
Click on any cell within the sparkline to show the Sparkline Tools menu.
In the Sparkline tools contextual menu, go to Sparkline Color > Weight and change the width of the line as you desire.
Save Time with Quick Analysis: One of the major improvements introduced back in Excel 2013 was the Quick Analysis feature. This feature allows you to quickly create graphs, sparklines, PivotTables, PivotCharts, and summary functions by just clicking on a button.
When you select data in Excel 2013 or later, you will see the Quick Analysis button Quick Analysis Excel Button in the bottom-right corner of the range selected. If you click on the Quick Analysis button you will see the following options:
1. Formatting
2. Charts
3. Totals
4. Tables
5. Sparklines
When you click on any of the options, Excel will show a preview of the possible results you could obtain given the data you selected.
If you click on the Quick Analysis button and go to charts, you could quickly create the graph below just by clicking a button.
If you go to Totals, you can quickly insert a row with the average for each column:
If you click on Sparklines, you can quickly insert Sparklines:
As you can see, the Quick Analysis feature really allows you to quickly perform different visualizations and analysis with almost no effort.
Data Jobs that Use Excel
Any position with the word “analyst” at the end of it requires Excel! That includes Data Analyst, Business Analyst, Business Operations Analyst, and Reporting Analyst.
Every reader contribution, however big or small, is so valuable for our future.
