Introduction to Excel
What is excel?
You have probably heard about excel. A popular spreadsheet that allows one to insert, read and manipulate data in rows and columns.
Starting with learning Excel is one of the major steps to a career in Data analysis. It helps you build essential skills such as, organizing, cleaning, summarizing, visualizing and interpreting data.
Excel interface:Ribbons,sheets,cells,columns,rows,
When you open your Excel workbook, you see a grid based space designed to organize, analyze and store data.The picture below is how the excel looks on default when opened.
Some of the notable features from the image are;
The Excel ribbon- it is the rows of tabs and icons at the top of excel that allows one to quickly find,understand and use commands to complete certain tasks. it contains the; file,home,insert,page layout,formulas,data,review,view tabs.
Columns- they run vertically top to bottom, identifiable by letters A,B,C...AA,BB,CC...
Rows- They run horizontally, left to right, identifiable by numbers 1,2,3...
Cells - A cell is where a row and a column intersect. identified by a cell reference e.g (A2,C5)
Worksheets- is a one page of data with an excel file.
Data types:Texts,dates and numbersoc
In excel data types, defines how excel displays,processes, and stores data in cells. The three most comomon data types used in data analytics are dates,numbers and texts.
Text Data type- this is used for information that is descriptive rather than numerical e.g names,countries,cities.
-
Number type data- This shows data that can be quantifiable i.e excel can calculate with. e.g Quantity sold, unit price and can be formatted as currency,decimal or percentage.
Entering and Editing data.
Accurate data entry ensures accurate and reliable,calculations analysis and reporting.
Entering data in excel.
Click on an empty cell,Type the value(date,text or number) then press enter button.
Editing data in excel.
You can edit data in a cell by double clicking on the cell then filing in with the right information.
Formulas and Functions in Excel.
Formulas are equations you create to calculate values in a cell.All formulas start with =.
functions are pre built formulas in excel that are used to perform common calculations.Commonly used excel functions are:
Sum used to add totals.
Average finds the mean of the values requested.
Min and Max Finds the lowest and highest values in a data set.
=MIN(A1:A10)
=MAX(A1:A10)
IF the if function in excel allows you to make a logical comparison between a value and what you expect.
AND/OR used to test multiple conditions.
=AND(A1>50, B1>50)
=OR(A1>50, B1>50)
CONCATENATE used to join one or two texts together. e.g.
=CONCATENATE(B2,"", C2)
It joins the contents in cell B2 to those in cell C2 while leaving a space in between.
Data cleaning and Sorting.
Data cleaning and sorting helps one to fix errors such as misspelled words, trailing spaces,improper cases, remove duplicates etc. we will dive in and see who to solve some of these issues
Remove duplicates
When you use the remove duplicate feature, the duplicate data is permanetly deleted. it is a common practice to copy the original data to another worksheet to avoid losing important information. below you are guided on how to remove duplicates
select the range of cells that has duplicate data you want to remove.
Go to Data at the top of your excel sheet
Click on remove duplicates then click Enter button.
Other times duplicate data can be useful, use conditional formatting to find and highlight duplicate data,review the duplicates and decide if you want the data removed.use the following steps:
Select cells you want to check for duplicates.
Select
Home,Conditional formating,Highlight cell rules,Duplicate values.
Convert text to numbers
Numbers that are stored as text can lead to unexpected results.To convert text to numbers you can use the value function by;
- Creating a new column, in one of the cells of the new column type
=VALUE()inside the parenthesis type the cell reference,next fill the cells formula down the other cells. #Sorting data. Sorting data in excel organises it alphabetically,numerically or by date. Select a cell in the column you want to sort. on the data tab, in the sort and filter group select how you want you data arranged.
Filtering data
it helps one to temporary hide some data you are working on so that you can focus on certain data.
To filter a range of data follow this procedure
Select any cell within the range you want to filter
On the tab select Data then filter
Select the column header arrow then select text filters or number filters then enter the filter criteria then okay.
Data Analysis with pivot tables,charts and dashboards
Pivot Tables
A pivot table is used to summarize,calculate and analyze data that lets you see comparisons,trends and patterns in you data.
To create a pivot table, select the cells you want to create the pivot table from,Select insert at the top tab then pivot table
It creates a pivot table based on the existing table
Select new worksheet then okay.
Pivot charts
Pivot charts compliment pivot tables by adding data visualization to the summary data in pivot tables.
They are created by
Click any cell in your data range.
Go to insert tab and click pivot chart.
pivot charts are used to;Analyze trends over time using line charts.
Compare categorical data with bar charts.
Display data proportions with pie charts.
Dashboards
They are interactive, visual summary of key data using charts,tables and KPIs in a single view for quick analysis.
Dashboards are create from a combination of pivot tables,pivot charts and slicers that are copied and pasted in a new worksheet.The below image showsa dashboard created from the combination of pivot charts,pivot tables and slicers.
Top comments (0)