- EXCEL ESSENTIALS FOR DATA ANALYTICS HOW TO
- EXCEL ESSENTIALS FOR DATA ANALYTICS SOFTWARE
We can sort the total sales from the highest to the smallest. Sort the results for a more visible look. To remove decimals, go to Home > General > Decrease Decimal. Here is a snapshot of the first few cities.Īpply the currency and decimal formatting using the shortcut CTRL + SHIFT + 4.
Double-click on the black cross until the values will appear. Hover the mouse until a black cross appears. Select the first cell with the sales value. 
Press “Enter” and the total sales for the first city will appear.Īuto-fill the formula in the remaining “City” columns.
Sum_range is the table name where your data is. The SUMIFS formula has the following criteria Once we have all the cities, we will use the SUMIFS formula to find the following: If you have Excel 365, you can use the UNIQUE function to extract the city names without duplicates. Our dataset remains with 430 distinct city names. Our sales analysis will help us identify the top/bottom performing city. We have sorted ours from highest to lowest.įrom the 5000 entries, 2238 had duplicated sales values. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Here is a snapshot of a few cells filtered by color. In our data of 5000 rows, 4993 products are above the average price. Go to Home > Conditional Formatting > Top/Bottom Rules > Above Average.įilter by colour to see all the above-average products. How many products are sold above the average price? Our data has 13 products sold 14 times each. To view the highest number of products sold, apply the “ Filter by colour” option to display the items. Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Which products had more than 10 quantities Sold? We’ll copy and paste our data into another spreadsheet before the conditional formatting to retain the original data for future use. For example, you can highlight all odd numbers from the highest to the smallest. We’ll use Conditional Formatting (CF) for the EDA.Ĭonditional Formatting in Excel allows you to make trends in your data more visible by creating rules that determine how the cells will be formatted and highlighted. Examples of data analysis problems to solve in Excel Exploratory data analysis (EDA)Įxploratory data analysis involves an in-depth investigation of your data to identify anomalies like missing values, discover trends, or draw summaries from columns.Īfter performing an EDA, you can now use your data to create final reports and visualizations. Go to Table Tools > Design > Table Name. 
Use the keyboard shortcut CTRL + T for Windows.Select any cell in the spreadsheet containing the data.A table eliminates the need for you to remember which cells contain your data.
EXCEL ESSENTIALS FOR DATA ANALYTICS HOW TO
How to approach a data analysis project in Excelīefore starting your data analysis, convert your raw Excel data into a table format. Final thoughts on using Excel for data analysis and visualisation.How to calculate descriptive statistics in Excel.How to create beginner level Excel dashboards.Sales analysis using pivot tables and charts.Examples of data analysis problems to solve in Excel.How to approach a data analysis project in Excel.Here is the Google Spreadsheet link for the data we’ve used. Our tutorial focuses on simple techniques you can use to analyze and visualize data using Excel for Windows, version 2013. You can use it to create, edit, view, share, and manage workbooks, tables, spreadsheets, and dashboards containing your data.
EXCEL ESSENTIALS FOR DATA ANALYTICS SOFTWARE
Microsoft Excel is a spreadsheet software usable in Windows, macOS, iOS, and Android.
0 kommentar(er)
