Excel is best tool for data analysis. This things are not thought academically. I have researched a lot to design this course.
This blog is for the learns which is beginning their data journey and data manager.
You can easily use Excel for analysis and visualization process.
I will start from very basic and very advanced level.
We will also learn about advance chart and graph.
Excel is made up of different row and column.
Vertical part is called column(represented by alphabets) and horizontal part is called row(represented by numbers).
Intersections of row and column combine and form a cell. Simple set of small rectangle that you see is cell
There are 3 sheet in excel.
But we can also increase sheet just by clicking plus icon that the bottom of excel.
Excel Window or opening file is corresponding to notebook
and the sheets is corresponding page in your note book.
There are 16,384 column and there are 1048576 row in excel.
Name cell is very easy simply we look at the intersection of row and column.
Altogether there are 17179869184 cell in excel sheep
Top portion is ribbon there are so many tabs in ribbon.
Let discuss about different tabs
Home tab
Here are so many option like
clipboard to copy cut paste
and styling many things
Insert tab
is used to insert elements into the worksheet like image, chart text box etc.
Page Layout tab
is used overall appearance of your worksheet and printing as well
Formula Tab
is used to insert formulas
Data tab
is used for data related commands like data validation, filtering , sorting and other advance tools
Review Tab
is to review you worksheet including spelling checking, translate or add comments
View Tab
is to see how our sheet is viewed for example zoom, freeze , arrange, macros(codes to make automatic)
we can easily add, delete and edit data in cell
we can also increase and decrease the cell of size
Work or manipulation does not affect the other sheet in excel its completely like a different page of notebook
We can also easily rename the sheet name just by double clicking on it
we can simply drag and drop the sheet
to order or manage sheet in work book
we can also insert row or column just by right clicking on the row or column
We can also increase the width of column and height of row
we can also zoom in and zoom out from the zoom option in the bottom right side of excel sheet
We can also hide and unhide sheet in excel just by right clicking on the sheet
We can also hide and unhide sheet by using format option of Home tab
Entering data in cell is very simple we can simply click on the desire cell and enter data
selected cell is highlighted and cell name is even shown in the top right of excel window
selected or heighted cell means if we type form keyboard we simply enter data in cell
Formulas in Excel
using formula in excel is very easy
we can do from very simple arithmetic calculation to very complex arithmetic calculation in excel
to put formula we should begin with equals to sign
and we can manipulate different values as per our requirements
We actually we the value in cell but we can see the formula in the formula bar which is at the top ,beside of cell name in excel
Basic Formula Operation
Formula is simply an expression from which we can do many ariuthematic calculation
=cell name + cell name for addition
=cell name * cell name for multiplication
=cell name - cell name for subtraction
=cell name / cell name for division
We expands the operations which is very use full
formula gets dynamic to corresponding cell reference and value gets automatically updated
if we want to sum constant along with sum we must make constant as constant
so we must stop the referencing of constant
For that we can use $ symbol eg $A1 so A1 cell what change in formula even if we drag or expand
Function
is pre define formula
for eg sum(range), average(range)
rank(),
sumproduct() function returns the sum after multiplying number in an array. It can also be used to count cells based on criteria provided.
rand() function returns random numbers greater than 0 and less than 1. The numbers change on recalculation.
rand function is used for throwing dice ,tossing coin etc
randbetween(bottom,top)
we can simply go to formula tab and see many functions
RANK was available in the older versions of excel. We can still use it, probably it will not be available in the coming versions of Excel. There are two new versions of Rank now - RANK.EQ and RANK.AVG. Below example may clear the difference between these two -
For values 5,6,6,7 -RANK and RANK.EQ will return ranks as 1,2,2,4 (Same rank for same values) and RANK.AVG will return ranks as 1 ,2.5 ,2.5 ,4. (Average of 2nd and 3rd rank). Because RANK.AVG takes average of the ranks where values are same, we often see decimal digits in ranks calculated using this formula.
If all the values in the list are unique, all the three formulas assign same ranks in that case.
Textual Function
trim( ) function removes spaces all before and following the words i.e. leading and trailing spaces and also removes extra spaces between words but does not remove the single space between words.
concatenate function joins several (more than 1) text string into 1 string
concatenation can also be done with the help of & symbol
substitute function is used to replace a part of text in string with something else. SUBSTITUTE function is used to clean data. This function is case sensitive
=substitute(text,old_text,New_text,[instance_number])
upper and lower function converts the entire text string into upper case and lower case letter
length function returns the number of characters in a string (text or numeric value). It also counts the spaces and special characters
left right and mid
this function returns the number of specific characters from the start/end of the string.
"MID" returns the number of specified character from the specified point.
=LEFT(Text,[num_chars])
=RIGHT(Text, [num_chars])
=MID(text,[starting_position),[num_chars])
Logical Function
AND OR
AND checks if all arguments are true and return TRUE value else returns FALSE value.
OR checks if any of the conditions are true or not and returns TRUE value else returns FALSE value
=AND(logical 1,logical 2,logical3 and so on..)
=OR(logical 1,logical 2,logical3 and so on..)
if
it check whether a condition is true or not and on the basis of that returns value
=if(logical test,[value if true],[value if false])
countif
this function returns the count of number of cells which consist of numbers and meet a given condition
=countif(range,count)
sumif function returns the sum of the cells which meet a given criteria
today/now
today function returns today's date in date format
now function returns current date and time in excel date and time format
day , month , year
day function returns the day of the months in numeric format
=day(date)
months() returns the month from a user supplied date
year() returns the year form a user supplied date
date and time diff
days() calculates the number of days between 2 dates
Datedif function is used for calculating the difference between two dats
=days(end_date,start_date)
or
=datedif(start date,end date, unit)
VLOOKUP(vertical look up)
VLOOKUP function looks up a supplied value in the first column of a table and returns the corresponding value from another column
=VLOOKUP(lookup_value , table_array , col_index_num , [range_lookup])
HLOOKUP(Horizontal Lookup)
HLOOKUP function looks up a supplied value in first row of a table and returns the corresponding value from another row
=HLOOKUP(lookup_value,table_arrray,row_index_no, [range_lookup])
INDEX
index function returns a reference to a cell that lies in a specific row and column of a range of cells
=index(array, row,[col_num])
MATCH
match function looks up a value in an array and returns the position of the value with in the array
=match(lookup_value, lookup_array,[math_type])
INDEX MATCH
index match is faster and lighter way than VLOOKUP
The older brother of the much-used VLOOKUP, INDEX MATCH allows you to look up values in table based off of other rows and columns
=index(range,MATCH(lookp_value,lookup_range,match_type))
Data tools
Sort
we can sort according to largest to smallest
smallest to largest
A======>Z
Z======>A
custom sort
you can sort either from sort and filter from home tab
or data tab also
Filtering
we can do
1. Text Filter
2. Number Filter
3. Custom Filter
4. Short cut key
we can go to sort and filter in home tab
or
short key is Alt + A + T
Text to columns
Types of data validation
1. Delimited
2. Fixed length
in data tab text to columns
delimited - characters such as commas or tabs separated each field
fixed width - fields are aligned in columns with spaces between each field, we can add and delete desire column
Remove duplicates
types of data validation
1. Exact
2. Column Specific
in date tab remove duplicate option
Formatting data and tables
changing font font size, color , shapes . borders
we can increase and decrease decimal value according to our need from home tab
conditional Formatting
is one of my favorite tool which provides up to visually highlight the data as per the rules that we have specified
In view tab
gridline is used to remove lines
if we uncheck the formula bar formula will not be visible to us
if we uncheck headings row and column name are removed
freeze planes to freeze the group of cell
Pivot Table
are very strong tools which are used to summarize large data sets
automatically group together similar variables to give out meaningful summary
go to Insert tab
click on pivot table
entire table area should be selected
either select same worksheet or new work sheet
on right sight there will be pivot table field
there are all Colum name and different fields in the right hand sides
We can simple drag column name desire field and look at the sheet and analyze our data
Analyze tab ==> Insert Slicer (similar to filter but much our interactive and easy)
Charts
Bar/ Column chart, Line Chart, Scatter Plot, Pie & Doughnut charts, Statistical Chart - Histogram, Waterfall, Sparklines
Best way to compare is chart
- compare item to other item
- compare data over time
- Make relative comparisons
- compare data relationships
- Frequency Comparison
- Identify "outlier" or unusual situations
Elements of Charts
Data series is the combination of several data points
Horizontal axis is aka categories axis
Legend is aka the information box which
data label and tile are also important elements
Grid line are basically the extension of vertical axis scales which makes easier the viewer to determine the magnitude of data points
Creating a chart is easy but creating a good chart that conveys message is difficult
We can simply create chart by selecting table going to insert tab and clicking on Recommended chart
Bar chart and column chart are most common chart
column chart are often used to compare discrete items
types of column chart
1 Cluster column chart
2 Stacked column chart
3 100% stacked chart
Its always good to start from scratch
we can add element simply by clicking on + icon which is along the chart
if bar is rotated than it is column chart
Formatting chart
As we know chart have several elements
to edit or format each element we can simply double click on element area
go go to Design or Format tab of chart tools
and start formatting
we can easily use fill and line, effect, size and properties
Line charts
are often use to plot continues chart
and are useful for identifying trends
Area Chart
very similar to line charts
Area below the line are colored in area chart
Pie and Doughnut chart
Pie charts is useful when we want to show relative proportions or contribution to whole
Pie chart can show only one data series
Pie chart are most effective with the small number of data points
Generally pie chart should not use more than 5 or 6 data points i.e. slices
Scatter chart and Bubble chart
to identify the relation between 2 variable
to draw a trendline
we can bring linear trend line on chart by simple clicking on + icon and checking trendline option
Frequency distribution
A frequency distribution for qualitative data lists all categories and the number of elements that belong to each of the categories
relative frequency of category= frequency of that category / sum of all frequencies
A bar graph is a graph made of bars whose height represents the frequencies of respective categories
frequency distribution for quantitative data lists all the classes and the number of values that belong to each class. Data presented in the form of a frequency distribution are called grouped data.
graph of frequency distribution of categorical variable is called histogram
These are the process
- Select the number of classes , usually between 5 and 20
- calculate the class width
classwidth = (maximum data value)- (minimum data value) / number of classes
Round this result to get a convenient number - Choose the value for the first lower class limit by using either the minimum value or a convenient value below the minimum
- Using the first lower class limit and the class width , list the other lower class limits
- List the lower class limits in a vertical column and then determine and enter the upper class limits
- Take each individual data value and put a tally mark in the appropriate class. Add the tally marks to find the total frequency for each class
Waterfall chart
It easy from 2016 version
if we are using office version before 2016 it difficult and is long process
Sparkline are great and we can make it easily to show a trend
type of sparkline
line
column
win/loss
Pivot Charts
Pivot charts are the graphical representation of pivot table
Named Range
we can name cell simply by going to cell name
so instead of using cell address we can simply used the name that we have assign
apart from cell we can also name the cell range
There are two reason to assign name to cell or cell range
1) When we assign name to cell or range that name can be cell in the drop box on cell name and use in formula directly
2) To revisit the cell or range directly from drop down of cell name
Indirect function
In excel indirect function returns a valid reference from a given text string
Shorts cuts
Excel short are very much similar to the other office package software
But there are lot more in excel
we can move around excel using arrow keys
to go to end of the range ctrl + arrow keys
one screen down Page down
one screen up Page Up
one screen right Shift + page down
one screen left shift + page up
to go to first cell in row just click home key
to go to first cell of sheet just click Ctrl + Home key
to select range click shift and arrow key
go to end of the range or table ctrl + arrow key (right)
ctrl + shift + arrow key go and select entire row or column
ctrl + Alt + V for detail paste option
F7 for spelling suggestions
to view all formula Ctrl + `((key below esc)
press Alt key and you can see many shortcuts
type word to implement shortcuts
Macros
Macro is set of programming instructions stored as the procedures. It contains sequence of code which performs certain actions. Action may be keys press on keyboard or mouse clicks.
so when ever we will run this codes all the action will be performed in the excel sheet.
creating macro
we need developer tab at the top
to make macro dynamic from selected cell we use click on use Relative reference in developer tab
than click on record macro
select the desire short cut key
and click on stop macro
then you can use your macro with shortcut on on cell
we can also use macro with button , bottom can be inserted from the insert option in Developer tab
Comments
Post a Comment