Today 47 of the Fortune 50 Companies rely on the IBM Cloud to run their business, and IBM Watson enterprise AI is hard at work in more than 30, 000 engagements. IBM is also one of the world's most vital corporate research organizations, with 28 consecutive years of patent leadership. Above all, guided by principles for trust and transparency and support for a more inclusive society, IBM is committed to being a responsible technology innovator and a force for good in the world. Syllabus - What you will learn from this course Week 1 Introduction to Data Analysis Using Spreadsheets In this module, you will learn about the fundamentals of spreadsheet applications, and you will be introduced to the Excel interface and learn how to navigate your way around a worksheet and workbook. 5 videos (Total 26 min), 1 reading, 2 quizzes Week 2 Getting Started with Using Excel Speadsheets In this module you will learn how to perform basic spreadsheet tasks, such as viewing, entering and editing data, and moving, copying and filling data.
It may seem a little confusing at first sight, but once you get the hang of it, it is really easy. This time, let me take the cryptocurrency market data I got from Yahoo Finance as an example. Vlookup can search for the first column and find the matching value in the second column. Let's say we want to find the symbol of cryptocurrency matching price 0. 0013. First, sort the info in ascending order. Second, select the two columns. Third, enter number "2" since we are trying to fetch data from the second column. Finally, choose FALSE to ensure an exact match of data. As you can see in the gif below, the corresponding value "ATB-USD" is returned. This is another simple but useful Excel formula that is widely used. When it is not feasible to count the cells one by one to find out how many of them meet your criteria, you may consider taking advantage of this formula: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) To understand it fully, imagine you are trying to figure out the number of male and female employees in each department of your company.
I have chosen to split the Date column and hence have specified / as the delimiter. In the third step, you can choose the location where you want Excel to paste the split data. As shown in the below figure the date will be split into three columns, which you can later rename as Day, Month, and Year. So without much hassle, the data is split up based on your requirement. Text to Columns These were some of the most commonly used Excel features that greatly help in data analysis and saves a lot of time while doing data preprocessing and reporting. I have used the same datasets to do analysis using Tableau and have written a couple of kernels in Kaggle on air quality analysis and Covid19 impact.
To slice out the data with the COUNTIFS formula, you may select the data range and the criteria. The first criteria range is the departments they are in, the second is their gender. In the example shown, the formula would be =COUNTIFS(F11: F21, F13, G11: G21, G13) The pivot table gives most people headaches. We all have heard how powerful it is but only a few can use it well. A Pivot table allows you to quickly summarize and analyze large amounts of data in lists and tables by dragging and dropping columns to different rows or columns. The columns can also be re-arranged as you wish. You may check out this video to learn how to create and analyze data with a pivot table. When you have a number of items with their quantities and prices in hand and you want to figure out their total sales, you may take advantage of this function. SUMPRODUCT multiples the quantity of each item and its price, and then adds up the sales of each item to deliver the total sales. Artículo en español: 10 Funciones Simples de Excel para El Análisis de Datos También puede leer artículos de web scraping en el Website Oficial Author: Milly
Learn useful Excel techniques and create powerful dashboards for exploratory data analysis Microsoft Excel is the foremost tool that was used for handling data by all industries before the advent of tools such as Tableau and PowerBI. Even today Excel is widely used for its powerful data analysis capabilities. In this article, I will walk you through some of the very useful Excel methods to analyse data and prepare a visually appealing dashboard to present to end users. For this section, I have used the Air quality data shared in Kaggle. Here is a snapshot of the data set, Air Quality Data set This data is s pecific to various pollutant levels including Air Quality Index (AQI) from 2015 to 2020 across various states of India. AQI is defined as an overall scheme that transforms weighted values of individual air pollution-related parameters (SO2, CO, etc. ) into a single number or set of numbers. AQI is useful for the general public to know air quality in a simplified way. AQI Category or Bucket is used to group the AQI values into six categories based on the value namely: Good(0–50), Satisfactory (51–100), Moderate (101–200), Poor (201–300), Very Poor (301–400), Severe (401–500).
Conditonal Formatting: Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value, depending upon the user's criteria. Charts: A simple Excel chart can say more than a big sheet full of information. As you'll see, creating charts in Excel is very easy and user-friendly, and is also used to make the data more colorful, presentable and interactive with user. Pivot Tables: Pivot table is one of Excel's most sublime and interactive features for representing data. A pivot table allows you to extract the significance from a large and detailed data set and view information more concisely. Tables: Tables allow you to examine and inspect your data in Excel quickly and effortlessly. What-If Analysis: What-If Analysis in Excel allows the user to try out different values using formulas and can be used in a conditional manner. Solver: Excel includes a tool called Solver that uses diverse methods from the operations research to find optimal solutions for all kind of decision problems and thus, makes the data more practical and usable for user.
We then progressively increase the difficulty as you move along in the course, culminating in advanced techniques taught through our case studies. If you do have an Excel background, you can skip or skim through the foundational sections and move directly into the more advanced topics. Stephen Dias - 5 Stars "If you're trying to learn the foundations of excel this course is all you need. " Brandon Parker - 5 Stars "I have a lot of experience building excel models and purchased the course for the case studies. I didn't expect to learn much from the introductory parts but was surprised to find additional keyboard shortcuts I had never learned as well as powerful new functions that will make my life a lot easier. This course helped me brush up on functionality that I often neglected while using excel and was super comprehensive. I look forward to continuing using this course as additional case studies are added and highly recommend it for even excel experts. " Learn through practice. Like most things in life, becoming proficient at data analysis in Excel takes practice.
Data analysis, also known as analysis of data or data analytics, is a method to clean, inspect, transform, model and introspect data with the aim to discover and decipher new information, form suitable conclusions, edit and format data accordingly as well as to validate the decision making process in a suitable and efficient manner. Data analysis has multiple facets, constituting a variety of diverse approaches under a variety of names, in different domains of life, that help to perform a variety of operations and validate the decision making process. Introduction This section illustrates the extensively useful and diverse features that Excel has to offer for analyzing data. Sort: You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order, depending upon the objective of the operation. Filter: Filter your Excel data if you only want to display records that meet certain criteria, and can be used to conditionally view specific cells, on the application of a condition.