Tables in Excel * Excel's Table command to convert a list of data into a named Excel Table. These tables have useful features, like sorting and filtering, to help organize and view data. * To convert any data into excel table :- Step 1: Hit Insert Ribbon and then hit table. Step 2 : Check or selected cells of the table and hit ok * Shortcut : Control + T * Repeat Step 2 After converting data into table you can anytime change name and default style of your tableBorders in excel * Your highlighted cell will get border. * Predefined cell border * Remove a cell border Create a custom cell border Shortcut method to assign default border to cell is ( Control + Shift + & )Most commonly used functions in excel are mentioned below: Basic functions in Excel Addition (+) Subtraction (- ) Multiplication (* ) Division (/) Power (*)greatlearning Basic functions in Excel Power & Square Root function + Method 1 — By using Power symbol ( * ) Example : A142 + Method 2 — Using the Power Symbol. Example : =A14(1/2) * Method 3 — Using Function. Example : SQRT(A1) — This won’t work for negative numbersgreatlearning Basic functions in Excel Percentage function + Method1 — By using percentage symbol ( % ) Example : =A1*50% + Method 2 — By using percentage symbol ( % ) Example : =A1*(1+B1) Here Al =50 , B1=50%, you will get value as (A1+50% of A1)reatlearning « Use + + keyboard keys to rewind or forward by 10 seconds Fens Form, Names in Formulas * Named Constant — Select Range of Data — On the Formulas tab, click Define Name. Give a name Say ‘Age’. — For using this range of data use ‘Age’ name Example: =Sum(Age) * Named Range — On the Formulas tab, click Define Name. © — Enter Name, Enter value to save constantgreatlearning Sorting * By Color — Select any cell of the colored column. — Go to Data Tab and hit Sort button. — Select column , in Sort on option select Cell Color / Font Color By multiple additions you can select priority of colors as well. * By values — Ascending or descending order by clicking respective buttons — Custom sort can also be done based on column priorityFiltering It means to fetch out data with certain similar characteristics. * Bycell color * By font color * By value — By text values , alphabetically — Bynumerical values (=, !=,>=,<=,>,<) For filtering we can also use AND and OR functions. Hit filter button or (control + shift+ L) for filter buttonsoreaticnrning Filtering It means to fetch out data with certain similar characteristics. * By cell color * By font color * Byvalue — By text values , alphabetically — By numerical values (=, !=,>=,<=,>,<) For filtering we can also use AND and OR functions. Hit filter button or (control + shift + L) for filter buttonsConcatenate in Excel Concatenate function is used to Join the strings. ¢ Using Concatenate Function — Example : = CONCATENATE(B8," ",C8,"",D8) “” is used to add space in between. * Using ‘&’ operator — Example: =B8&""&C8&""&D8 * Concatenate String and numerical — Example: ="Your billis" & B16Left, Right in Excel * Left function returns the number of characters defined from the string from Left. — Syntax : =LEFT(Text, number of characters) — Example 1: =LEFT([@Column name],3) — Example 2: =LEFT(“Text”, 3) * Right function returns the number of characters defined from the string from Right. — Syntax : =RIGHT(Text, number of characters) — Example 1: =RIGHT([@Column name],3) — Example 2: =RIGHT(“Text”, 3)Trim in Excel + TRIM function returns a text value with the leading and trailing spaces removed. It is also used to remove unnecessary spaces between words in a string. — Syntax : =TRIM("Text”) — Example 1:=TRIM(" Hello! How are you ??") — Example 2:=TRIM(“Hello! How are you???”)reatlearnir ee eed re CR RCE aac conn fer Lower & * Lower Function converts strings in lower Case. : SLOWER("Text”) — Example 1: =LOWER(“HELLO! How are You?”) > hello! how are you? — Synta * Upper Function converts strings in upper Case. — Syntax : =UPPER("Text”) — Example 1 : =UPPER(“hello! How are You?”) > HELLO! HOW ARE YOU? * Proper Function converts strings in Proper Case. — Syntax : =PROPER("Text”) = Fxample 1 : =PROPER(“hello! HOW are You?”) } Hello! How Are You?greatlearning Find & Search in Excel Find function is used to find position of the particular substring within a string. — Syntax : =FIND("Find text”,"within text”) — Example : = FIND(“Hello”,”"Hello!,How are you?”) > 1 — Example : = FIND(“hello”,"Hello!,How are you?”) > ERROR — Find is CASE SENSITIVE * Search Function is used to find substring with in the string. — Syntax : =SEARCH("Find text”,"within text”) — Example : =SEARCH("hello”,/”Hello!, How are you?”)> 1 SEARCH("I?0 “,"Hello!, How are you”) > 3 — ?s used as any single character and * can be used as any series of characters — SEARCH is CASE INSENSITIVE — Examplegreatlearni 9 Learning for Substitute & Replace in Excel * When we know the text to be replaced we use Substitute function. UBSTITUTE(Cell name , "Old text”, "New text”, Instance) — Example : = SUBSTITUTE(C1,"Hello”,”Hi”) > Hello will be replaced by Hi — Example : = SUBSTITUTE(C1,"Hello”,”Hi”,2) 2" Hello will be replaced by Hi — Substitute is CASE SENSITIVE = Syntax : * When we know the position of the text to be replaced we use Replace Function. — Syntax : =REPLACE(Cell name , start number, number of characters , New text”) — Example : = REPLACE(C1,4,3,’Hi”) Starting from 4" position & length 3 will be replaced by Hi.sreatrcarrure: Round in Excel Itreturns a number rounded to a given number of digits. The ROUND function can round to. either left or right of the decimal point. * syntax : =ROUND (number, number digit) — Example : = ROUND(6.7845,1) > 6.8 Rounding to nearest 1 decimal place — Example : = ROUND(6.7845,2) } 6.78 Rounding to nearest 2 decimal place = ROUND(6.7845,3) > 6.785 Rounding to nearest 3 decimal place = ROUND(6.7845,0) > 7 Rounding to nearest whole number ROUND(9518.5,-1) - 9520 Rounding to nearest 10 Example : = ROUND(9518.5,-2) > 9500 Rounding to nearest 100 — Example : = ROUND(9518.5,-3) > 10000 Rounding to nearest 1000 — Example — Example — Example :Vie ee tie Floor in Excel * Itrounds a given number down to the nearest specified multiple. * Syntax : =FLOOR (number, multiple) — Example : = FLOOR (232,3) > 231 Rounding to nearest multiple of 3 — Example : = FLOOR (324,2) 324 Rounding to nearest multiple of 2 — Example : = FLOOR (441,5) > 440 Rounding to nearest multiple of 5 — Example : = FLOOR (654,100) > 600 Rounding to nearest multiple of 100 — Example : = FLOOR (-7.8,-2) > -6 Rounding to larger nearest multiple of 2. — Example : = FLOOR (-7.8,2) > -8 Rounding to smaller multiple of 2. Note : This works only with the negative numbersgreatlearning AND & OR in Excel AND means when both of the conditions are True then only it is True other wise it is False. OR means when any one of the condition is True then its True or when both of them are False then its False. Below is the example of AND and OR operators Type 3 : Using AND Operator — Example = =IF(AND([@Maths]>40,[@Physics]>40),"Pass","Fail") Type 4 : Using OR Operator — Example = =IF(OR([@Maths}>40,[@Physics]>40),"Pass","Fail”)Conditional Formatting * It enables you to highlight cells with a certain color, depending on the cell's value. — With Highlight Cells Rules we can highlight cells greater/less/equal/not equal than certain value. — With Top/Bottom Rules we can highlight top/bottom 10% cells , etc. Conditional Formatting button is present in home tab of Excel.V Lookup in Excel It is used to make a exact match or approximate match and find values from leftmost Column. + Syntax : =VLOOKUP(Look Up value, Table Array , Column Index number , range look up bool value) + Example : =VLOOKUP(D12,A3:E8,5,FALSE) + 1°* Argument - D12 will have value to be matched. Here say 103 + 2 Argument - A3:E8 would be the table in which the value would be matched in leftmost Column. + 34 Argument - St Column of the selected table + 4 Argument - False means ( Exact Value ) If True then ( Approximate Value ie max value less than the 4th argument)greatlearning V Lookup in Excel It is used to make a exact match or approximate match and find values from leftmost Column. * Syntax : =VLOOKUP(Look Up value, Table Array , Column Index number , range look up bool value) * Example : =VLOOKUP(D12,A3:E8,5,FALSE) * 15 Argument - D12 will have value to be matched. Here say 103 + 2" Argument - A3:E8 would be the table in which the value would be matched in leftmost Column. * 3° Argument - 5‘ Column of the selected table + 4th Argument - False means ( Exact Value ) If True then ( Approximate Value i.e max value less than the 4th argument)H Lookup in Excel HLOOKUP function always looks up a value in the topmost row of a table and returns the corresponding value from a column to the right. * It is Case-insensitive In case of multiple matches it will take First Match If no value is matched it returns nullH Lookup in Excel Itis used to make a exact match or approximate match and find values from topmost row. * Syntax : =HLOOKUP(Look Up value, Table Array , Column Index number , range look up bool value) + Example : =HLOOKUP(D12,A3:E8,2,FALSE) + 15' Argument - 012 will have value to be matched. Here say 103 + 2% Argument - A3:E8 would be the table in which the value would be matched in topmost row. + 3 Argument - 2" row of the selected table + 4% Argument - False means ( Exact Value ) If True then ( Approximate Value i.e max value less than the 4th argument)V Lookup in Excel VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right. * It is Case-insensitive * Incase of multiple matches it will take First Match * Ifo value is matched it returns nullMatch in Excel + Itreturns the position of a value in a given range — Syntax : =MATCH (value ,lookup range) — Example : ==MATCH(104,A3:A9) Here it the function would return us the position of he matched value from the range.Index & Match in Excel * To perform advance lookups we can use INDEX & MATCH function together. * We can get the index value from MATCH function and then use this value in INDEX function for getting the value. — Example : =INDEX(D3:D9, MATCH(103,A3:A9)) Here, match function will return value of row with respect to 103 in range A3 to A9 then this 5 will be used by Index function to lookup in 5‘ row of range D3 to D9.Index in Excel + Itreturns returns a specific value in a one-dimensional range with reference to index. — Syntax : =INDEX(lookup range, lookup index) — Example : =INDEX(A3:A9,F19)-> Here it would look for value of F19 index in range from A3 to A9.Dropdown in Excel + Instead of typing you can take values from users as a list of data in the form of Dropdown menu. * Steps to add a Dropdown. — Select a cell where you want to make a dropdown in Sheet 1 — make a list of cities in different cells in Sheet 2 which you want as options in dropdown Favorite City Shoot 2 Block 4] + sheet 1Learning Objectives Bar Plot Pie Chart Line Chart Combination Chart Scatter Plot Pivot Charts Radar ChartPivot Table in Excel A pivot table allows you to extract the significance from a large, detailed data set. It can be used as a Data aggregating function. * We can create new table using pivot Steps to make a Pivot Table Select any data cell from the table. In insert tab hit PivotTable By default you will get a blank pivot table in new sheet. Drag fields in row, columns, values and filter area. (Columns for 2d pivot) To Sort the table right click any cell and hit sort button. Click the filter drop-down for filtering.Calculated fields in Excel A calculated field is calculated from the values of another field. Steps to make a Pivot Table — Select any data cell from the pivot table. — In PivotTable Analysis tab hit Fields, Items & Sets — Select Click Calculated Field — Enter name, Type formula , click AddSlicers in Excel It is used to quickly and easily filter pivot tables. * Steps to make a Slicers — Inanalyse tab hit Slicer — Select the parameter on the basis of which you want to slice.Pie Chart in Excel It is used when we have one categorical data and one numerical data. Generally, frequency is used as a categorical data. Pie Charts are convenient to get idea of overall distribution out of 100% For Adding labels hit “Add Chart Element” button in the top left corner. For Changing colors of the particular segment hit the section twice and then edit. Different types of charts in pie chart can be selected from the options menu.Pie Chart in Excel Population Distribution across cities 2% |Bar Plot in Excel It is used when we have one categorical data and one numerical data. * Generally, frequency is used as numerical data. * For Adding labels hit “Add Chart Element” button in the top left corner. * For Changing colors of the bar hit “Change colors.” * Different types of charts in Bar chart can be selected from the options menu. * Side by side column chart are also useful for comparative analysis.Line Chart in Excel Line charts are used to display if you have text labels, dates or a few numeric labels on the horizontal axis. * Generally, Date is used as a categorical data. * For Adding labels hit “Add Chart Element ” button in the top left corner. * For Changing colors of the Line hit “Change colors.” + Different types of charts in Line chart can be select from the options menu. + Multiple line chart are also useful for comparative analysis.Scatter Plot in Excel It is often used to show the relationship between two numerical parameters. * Linear line can also be drawn in scatter chart from “Add Chart Element” menu. * For Adding labels hit “Add Chart Element” button in the top left corner. * For Changing colors of the Line hit “Change colors.” * Different types of charts in scatter chart can be select from the options menu.Scatter Plot in Excel Relationship between Experience and SalaryPivot Charts in Excel Age (all) = Gender (all) <] Row Labels ~/Average of Total Pivot chart for average marks state wise Bangalore 43.065 7 Chennai 52.47 Delhi 65.01 “ Goa 60.72 50 Mumbai 49.5 ‘e Pune 45,54 steal Grand Total 51,33857143, “ 20 ° Bangalore Chennat Debi MumbaiRadar Chart in Excel Radar Chart aay Sakshi a Sumit ashen PriyaCombination Chart in Excel 50 40 30 20 10 ‘Sumit Combination Chart Rahul Priya Rashmi mmm Age: ——Maths Raj ‘Sakshi 90 80 70 60 so 40 30 20Pivot Charts in Excel Pivot Charts are use to analyse the data with in Pivot table. Steps to get pivot chart are: = Click any cell of pivot table — Goto PivotTable Analysis — Hit Pivot Chart button — Hit graphs elements to make changes in the graph Learning foLine Chart in Excel Average Temperatue ( degree Celcius) 0 as 20 5 ° January February March April-May tune——ly_—_—September Octomber November December em hune eurOffset in Excel * Itreturns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. — Syntax : =OFFSET(reference, row, column, height, width) — Example : =OFFSET(A2,3,3,1,1) reference point will be A2, It will move 3 rows below and 3 columns right. Fetch 1 value in height and 1 width reference value. — Example : =SUM(OFFSET(B2,2,2,2,1))> reference point will be B2, It will move 2 rows below and 2 columns right. Fetch 2 values in height and 1 in width and return sum.