WordPress.com



Unit I - Introduction to Advanced Excel Functions1.1 Formatting - RecapFor Selection Ctrl + down ArrowCtrl + Up ArrowInsert & Delete RowCtrl + Shift + + for add rowCtrl + - for remove rowSelecting all row and ColumnCtrl + Shift & Down arrow for all row then Ctrl + shift + right arrowBordering the Data/TableGo to Font Section Select Click All Borders, Outline Border, Thick BordersCtrl + Z for UndoHeader ColoringSelect First Row & Go to Font Use Font Color and Fill ColorMerge & CentreAlignment FunctionsText Options – Bold, Italic, Font , Increase/ DecreaseNumber FormattingCurrency for Total T/O (Cr.), Increase/Decrease DecimalPercentage for AdvancesWrap Text1.2 Formatting & FormulasCount CompaniesUse =CountTACount Companies starting with VowelsUse = Countif(B8:B76, “A*”) + Countif(B8:B76, “E*”) + Countif(B8:B76, “I*”) + Countif(B8:B76, “O*”) + Countif(B8:B76, “U*”)Total of All ColumnUse = Sum for each Column to get totalFind Max of Amount FinancedUse = MaxFind Min of QuantityUse = MinUse SUMPRODUCT (Use column C and D)Use =Sumproduct(Select Col –C, Select Col D) Value errorFind AverageUse Average FunctionSUMIFSelect range, write criteria, sum rage and Enter=SUMIF(range, criteria, sum_range)SUMIFS ( for Mutlitple Conditions)=SUMIFS(sum_range, criteria_range1,criteria1)AVERAGE=AVERAGE(number1,number2…..numberN)AVERAGEIF=AVERAGEIF(range, criteria, average_range)1.3 Text to ColumnsSelect Data -> from A7 to A2968Goto Data Tab -> Select Text to Columns under Data Tools -> Select Delimited -> Select Next -> Click others and type “ | “ Symbol - > Select Next - > Select Destination as “$B$7” then Click Finish.1.4 CHOOSE FunctionType the Months from C7 to C18Goto Cell E7 then Type = Choose (Index_num, Value1,Value2,Value3…..Value4)=Choose(E6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18)Then, type 1/2/3/4 in E6, you will get Month displayed in E7)1.5 Basic Lookup FunctionsEquity Capital for SBIN =VLOOKUP(B61,$A$8:$K$58,4,0)Free Float for ZEEL =VLOOKUP(B63,$A$8:$K$58,5,0)BHEL Beta =VLOOKUP(B65,$A$8:$K$58,7,0)Company whose Free Float is 19938.9 =VLOOKUP(B67,CHOOSE({1,2},E8:E58,B8:B58),2,0)1.6 Advanced Lookup FunctionsScript Name for Wipro Ltd. =MATCH(B61,B8:B58,0) (or) =INDEX($A$8:$K$58,C61,1)Script Name whose Free Float is greater than 35000 Crores =INDEX(A8:K58, MATCH(B63,E8:E58,-1),2)Calculate weightage beyond AXISBANK =SUM(INDEX(A8:K58,MATCH(B65,A8:A58,0),6))1.7 Named Range & Dynamic Named Range OFFSETSelect Cells from B6 to B 13 – Go to Formula Tab – Select Define Name – Write Name as “Price” - Refers to ='Named Range'!$B$6:$B$13 Click OKTo Perform SUM using Range in Cell B19 = Sum(Price)Prepare Script List – in Cell B61 – Go to Data Tab – Select Data Validation – Select List – Select Source = A8:A58 (or) Select the Script List – Define Name as “Script” – Go to Data Validation – Select List – Source =Script - Drop menu will appear.Prepare Companies List – in Cell B63 - Select the Companies List (B8:B58) – Define Name as “Company” – Go to Data Tab – Select Data Validation – Select List – Select Source = Company - Drop menu will appear.Prepare Header List in Cell B65 - Select the Headers (A7:K7) – Define Name as “Header” – Go to Data Tab – Select Data Validation – Select List – Select Source = Header - Drop menu will appear.Select all Data points one by one Define Name with its respective HeaderPrepare Dynamic List - Go to Data Tab – Select Data Validation – Select List – Select Source =INDIRECT($C$65). Drop menu will appear.1.8 Dynamic Named Range INDEXPrepare Script List – in Cell B61 – Go to Data Tab – Select Data Validation – Select List – Select Source = A8:A58. Drop menu will appear.Prepare Companies List – in Cell B63 – Go to Data Tab – Select Data Validation – Select List – Select Source = B8:B58. Drop menu will appear.Prepare Dynamic List – Go to Formula Tab – Create a New Define Name – Name as “Script1” – Referes to ='Dynamic Named Range INDEX'!$A$8:INDEX('Dynamic Named Range INDEX'!$A:$A,MATCH("ZEEL",'Dynamic Named Range INDEX'!$A:$A,0)) – Click ok – Go to Data Tab – Data Validation – Select List – Source = Script1. Drop down menu will appear.1.9 Data ValidationGo to Cell E8 – Data Tab – Data Validation – Select List – Source = I7:I10In Data Validation – Check the Error Alert Style – Select as WarningCopy the same and paste up to Cell E58Type Irrelevant options like Why? or What? in few CellsGo To Data Validation – Select Circle Invalid Data – Red Circle will appear in the invalid data1.10 Array FunctionsTo Check data points are True/False using > or <Sum to compare achieved and target =SUM((D6:D13>=C6:C13)*(C6:C13>0)) along with this to get Count use Ctrl+Shift+EnterIn Function Argument (Fx) the formula will appear like this – {=SUM((D6:D13>=C6:C13)*(C6:C13>0))}To find the Total Achieved use SUMIFS function =SUMIFS(D6:D14,A6:A14,G5,B6:B14,G6) (or)Use =SUM(IF((A6:A14=G5)*(B6:B14=G6),D6:D14)) & Ctrl + Shift + EnterTo check the Column A/ or List is Unique or not use =IF(MAX(COUNTIF(A$6:A$12,A6:A12))>1,"List is not Unique","List is Unique") & Ctrl + Shift + EnterIf the List contain repeated text it appears as "List is not Unique" otherwise it will appear as "List is Unique"To Find out Sales Person A’s Output for 3 months – Jan, Feb, Mar =SUM(VLOOKUP(C16,A7:M14,{2,3,4},0)) & Ctrl + Shift + Enter1.11 Circular ReferenceType Sales, Expenses, Tax values Find Profit by using the Formula = B6-B7-B8-B9Find Other Expenses as 10% of Profit – Instead of Value Circular Reference Warning will get To Remove Circular Reference Warning – Go To File – Select Formulas – Select Enable iterative Calculation – Click OK – Other Expenses will be calculated and appear in Cell B8Unit II- Advance & Dynamic Charts2.1 Recap – Line ChartSTEP 1: Select the entire data.STEP 2: Go to INSERT, CHARTS.STEP 3: Select the line chart to represent Stock Price.2.2 Change in MarkerSTEP 1: Select entire data, go to INSERT, CHART, and SELECT the LINE CHART with MARKERS.STEP 2: Click on the MARKER( a format data series box will appear) , Select FILL CAN ICON from there.STEP 3: In the FILLCAN ICON , SELECT MARKER , then select MARKER OPTION . Select “BUILT-IN” from the option and you can change the type.center48525800STEP 4: You can change the color and size of the MARKER too.2.3 Dotted LinesSTEP 1: Select the entire data , INSERT , CHART , INSERT LINE CHART WITH MARKS.STEP 2: Select one Marker , go to FORMAT DATA POINT dialog box.STEP 3: In the LINE option , change the DASH TYPE to Dotted lines(--------------), you can change and increase the width of the line and change color.Dotted lines in between the normal lines will appear.2.4 Conditional Formatting In GraphSTEP 1: Create a column signifying sales >100 and < 100 using “if” statement.Sales >= 100 [=if(C5>=100,C5,0)], then drag.Sales<100 [=if(C5 < 100,C5,0)], then drag.STEP 2: Select the above made table , go to INSERT , CHART , SELECT a COLUMN CHART2.5 Thermometer Column ChartSTEP 1: Select the data , INSERT CHART , Select STACKED COLUMN CHART .STEP 2: Select the bar , RIGHT CLICK ON IT , go to FORMAT DATA SERIES , In SERIES OPTION Select PRIMARY AXIS. INCREASE THE “OVERLAP” to 90% and DECREASE THE WIDTH to 40%.STEP 3: Go to SECONDARY AXIS IN FORMAT DATA SERIES OPTION and carry out the same process as PRIMARY AXIS .STEP 4: Select the bar to change color if necessary.2.6 Bullet ChartSTEP 1: Select the data , go to INSERT, CHART , SELECT THE STACKED COLUMN CHART .STEP 2: Go to “ CHANGE CHART TYPE “ (a chart with various color , stacked on each other will appear)STEP 3: Select the “ ACTUAL” part of the column chart , go to “CHANGE CHART TYPE”, “SECONDARY AXIS”.STEP 4: Change the chart type to marker-lined-line-chart. STEP 5: To change the MARKER , select the MARKER , go to FORMAT DATA SERIES , select FILL CAN ICON STEP 6: SELECT “MARKER OPTIONS” , SELECT “BUILT-IN” , change the type of MARKER , also change the size of the MARKER.STEP 1: Select the entire data , go to INSERT , CHART , SELECT RADAR CHART.A spider shaped chart would appear.2.8 Bubble ChartSTEP 1: Select the entire data , go to INSERT , CHART , SELECT BUBBLE CHART A bubble chart showing revenue from mobile phones in various countries will appear.2.9 Stacked Column ChartSTEP 1: Select the entire data , go to INSERT , CHART , STACKED COLUMN CHART.A stacked chart will appear representing total score in science and maths.2.10 Waterfall ChartSTEP 1: INSERT 5 COLUMN between the headings and INSERT 2 ROWS below the headings.STEP 2: NAME THE COLUMN AS BASE, END , DOWN , UP , START.STEP 3: LINK FIRST amount of SALES to “START” first cell i.e, link G6 to F6.STEP 4: FOR “UP”, use syntax [=max(G6,0)],drag till DECEMBER.STEP 5: FOR “ DOWN”,use syntax [=min(G6,0)],drag till DECEMBER.STEP 6: Select the “BASE” value and type it in cell B4 and B18.STEP 7: FOR “BASE” value , exclude JANUARY cell and begin with FEBRUARY cell. Use syntax [=sum(JAN+DOWN VALUE OF JAN+START VALUE OF JAN)-UP VALUE] , then drag it down.STEP 8: CUT and PASTE December value from cell B17 to C17.STEP 9: NOW SELECT the entire newly created data, from MONTH to START, go to INSERT, CHART, and STACKED COLUMN CHART.STEP 10: GO to column with the “BASE” value and change its color to WHITE SO AS TO MAKE IT INVISIBLE.A chart in the form of WATERFALL will be created.Unit III – Dash Boards3.1 Creating Dash Board Using SlicerCreate a New Worksheet and name as DashBoard1Create Slicer – Select Data – Insert Tab – Pivot Table – New Worksheet – Name as Working Sheet1Select Company – Move it to Filter. Go To Analyze Tab under Pivot Table Tools – Insert Slicer – Click on Company and Vairbale – You will get vertical slicer. Convert it to Horizontal – Select – Options Tab – Increase Columns – 4 for Companies and 5 for Variables – Copy and paste both to Dash Board1 Worksheet (Adjust the Width and Height)Create New Worksheet & name it as Working Sheet2 – Select entire Data – Insert Tab – Create Pivot Table – Save this with Working Sheet2Create two new slicers for company and variable – Arrange as HorizontalGo to Dash board1 – In 10th Row select three columns each for 2011, 2012, 2013, 2014, 2015Select data – insert tab – Create pivot tab – Choose existing worksheet – Working sheet1 – Click OKSelect Company from Rows & Year from Values use Variable as filter do the same for 2011,2012,2013,2014,2015 in the same sheetLink Company from workingsheet1 with Dashboard1 and drag down for each year. For get the value under each year – Perform VLOOKUP Function =VLOOKUP(B8,Sheet3!A4:B7,2,FALSE) and drag it down. Do the same for 2012,2013,2014,2015To Connect Slicer with Data Table – Select Slicer – Options – Select Report Conncetions – Go to WorkingSheet1 – Select Pivot Table 3,4,5,6,7 – Click OK – OUTPUT change as per Selected VariableConditional Formatting with Data Table – Go to Cell B15 – Write a condition =B8='sheet 1'!$B$2, drag it down up to Cell B18. Similarly do for all the 5 yearsGo to conditional formatting – Click manage rules – New rule – Use a formula to determine = B15 – Click format – Select Green Colour applies to all parts (B8:B11, F8:F11, I8:I11, L8:L11, 08:011) – Similarly apply the same using format painter to all the years – Use different colours for each year.3.2 Dash board – Lookup FunctionCreate a New Worksheet – name it as Dashboard2Create a Slicer using Pivot table for Company and VariableKeep Company Slicer in Horizontal Top and Variable Slicer in Vertical LeftType 5 years – 2011,2012,2013,2014,2015 in Horizontal below to the Horizontal Slicer Below the year write an INDEX Function =INDEX(Data!$A$7:$A$26,MATCH(workfile!$C$1,Data!$A$7:$A$26,0),)To Get variable in the Cell use OFFSET and MATCH Function along with the INDEX Function =OFFSET(INDEX(Data!$A$7:$A$26,MATCH(workfile!$C$1,Data!$A$7:$A$26,0),),MATCH(workfile!$M$1,Data!$B$7:$B$26,0)-1,2)3.3 Customer Call Center Dash BoardTASK #1STEP1: Create three worksheet.STEP2: Rename the worksheet as Dashboard, Unique, Working.STEP3: Go to unique worksheet and create header as product, region, Customer_type, agent_id, chart_type.STEP4: Go to data sheet then copy product item and paste it in the unique sheet.STEP5: Select product entirely and go to data tab then click sort and choose A to Z. Click remove duplicate under product header.TASK #2STEP1: Go to dashboard worksheet then type short date in B4 and long date in B5And days in F5.STEP2: In cell C4 type =today () .STEP3: To design go to Insert tab and click illustration and select shapes.STEP4: Select product, region, customer type, agent_id, chart_type which is required for the GraphSTEP5: Go to developer then click insert and select combo box, right click then a dialog box Appears. In input range go to unique sheet and select entire product.STEP6: To link it go to working sheet link with B2 and change drop down lines depend of item. Similarly do for region, customer type, agent_id, chart_type.STEP7: Remove gridlines then go to file option and select advanced choose remove and tick the Check box of show gridlines. TASK #3STEP1: Go to developer tab and choose insert and click option button, create 4 option Button. STEP2: Right click each option button and format control.STEP3: Dialog box appears, link the cell and go to working sheet and link it with F2 cell number.Give header for F1 as output1. Similarly choose group box and rename as choice. TASK #4STEP1: Go To Working Worksheet - Under snapshot create the following header as total call, spark line, total_talk, Avg_ call_duration, resolution_rate, satisfaction, upsell$.STEP2: Go to working sheet from cell number B10 create the following header as Date, total_calls, talk_time, avg, call_duration, resolution_rate,satisfaction,upsell$.STEP3: In cell B11 link the starting date in dashboard. And calculate total calls on each day In working sheet. Type =countif (range, criteria), =countif (data, k6:k14837)STEP4: Calculate talktime by using sumif formula =sumif ((k6:k14837, b15, f6:f14837)/60/60)STEP5: Calculate resolution rate using countifs formula =countifs (data range, date, resolved range,”yes”)/281; =countifs (data! $k$6:$k$14837, working! B14, data! $G$6$:$G$14837,”yes”)\C14STEP6: Calculate satisfaction using sumifs formula =sumifs (satisfaction range, data range, date) STEP7: Calculate upsell$ value =sumifs (upsell range, data range, date) TASK #5STEP1: Create a named range in data sheet for customer, region, and agent.STEP2: Then to calculate the no: of desktop use countifs =countifs (date, working choose (G5), product, region, customer, agent), link with D13STEP3: Then for talktime use sumifs (sum range, criteria range, criteria, choose (o/p1, Product, region, customer, agent), G13/60/60STEP4: Resolution rate =countifs (date, working choose (G5), product, region, customer, agent), link with D13STEP5: For chart creation select 3 data from working sheet (i.e.) data, option 1, option2 Insert line chart, copy the chart1 and using the paste special paste it into dashboard (i.e.) Linked picture, Refers=choose (charttype (working sheet), chart1, chart2, chart3, chart4, chart5). Unit IV – Macros4.1 Recording Macro – FormattingType data points in Column A Go to developer tab – Record Macro – Name Macro as Formatting – Use Short cut as Ctrl + QStrat recording the Macro – Select the Data Point – Copy Paste the data in to Column D – Center and Middle Align the data – Fix Border – Fill Color inside the data – Stop recordingDelete the Column D – Run Macro through Developer (or) Use Short Cut Key – Ctrl + Q4.2 Recording Macro – Basic Math functionsType math output title in Column D – Total Output, Mean1, Mean2, SD 1, SD 2Go to developer tab – Record Macro – Name Macro as Basic_Math – Use Short cut as Ctrl + WIn Cell E7, find output by using SUMPRODUCT function = SUMPRODUCT(Array1, Array2)In Cell E8 & E9, find Mean 1 and Mean 2 by using Average functionIn Cell E10 & E11, find SD 1 & SD 2 by using STDEVA functionRun Macro through Developer (or) by using Short Cut Key – Ctrl + W4.3 Find StringGo to developer tab – Select Visual Basic for writing CodeWrite i as Integer, iRowNumber as Integer and sFindText as String and iRowNumber = 0Loop through Cells A1 – A100For I = 1 TO 100, Write IF Statemnet up to Exit For and End If If iRowNumber = 0, Set a Message Box with “ String” & sfindout & “Not Found), Else – MessageBox with “String "String " & sFindText & " found in cell A"Run Macro through Developer4.4 Math functionsGo to developer tab – Select Visual Basic for writing CodeWrite i as Integer, Col As Range, dVal As DoubleSet the variable 'Col' to be Column A of Macro 3Loop through each cell of the column 'Col' until a blank cell is encounteredApply arithmetic operations to the value of the current cellThe command into Column A Cells(i, 3) = dVal, i = i + 1, LoopRun Macro through Developer4.5 Fibonacci numberGo to developer tab – Select Visual Basic for writing CodeWrite i as Integer, iFib As Integer to Store current value, iFib_Next As Integer next value, iStep As Integer to store next step sizeInitialize the variables i = 2 and iFib_Next = 0Do While loop to be executed as long as the value of the current Fibonacci number exceeds 1000If I = 2 then iStep =1 and iFib =0, Else = iStep = iFib, iFib = iFib = iFib_Next End ifCalculate the next value in the series and increment - iFib_Next = iFib + iStep, i = i + 1, LoopRun Macro through DeveloperUnit V – Statistical Functions5.1 ShortcutsLearn and use the following important shortcuts in MS ExcelNo FunctionsShortcuts1To remove the selectionEsc2Close a spreadsheetCtrl + W3Open a spreadsheetCtrl + O4Go to Home TabAlt + H5Save a spreadsheetCtrl + S6CopyCtrl + C7PasteCtrl + V8UndoCtrl + Z9Remove cell contentsDelete10CutCtrl + X11Choose to fill colorAlt + H + H12Go to Insert TabAlt + N13BoldCtrl + B or Alt + H +114Center Align contentAlt + H + AC15Go to page layoutAlt + P16Go to dataAlt + A17Go to viewAlt + W18Add bordersAlt + H + B + options19Delete columnCtrl + (-) or Alt + H + D + options20Go to formulaAlt + M21Hide rowsCtrl + 922Hide columnsCtrl + 023Edit cellF2 or Fn + F224Go to next cellRight Arrow or Tab25Go to previous cellLeft Arrow or Shift + Tab26Move upUp Arrow27Move downDown Arrow28Enter current timeCtrl + Shift + :29Enter current dateCtrl + ;30Go till the endPg Down or Ctrl + Down Arrow31Go to startHome32Go to first filled cellCtrl + Left Arrow33Go to last arrow and select as wellCtrl + Shift + Down Arrow34Selecting the cell one by oneShift + Down Arrow/Up/Right/Left35Insert Row or ColumnsCtrl + +36Insert Row or ColumnsCtrl + +5.2 Forecast and TrendForecast FunctionFind or Forecast value for 2013,2014 and 2015 by using Forecast functionGo to Cell H3 use forecast function = FORECAST(H$2,$D3:$G3,$D$2:$G$2) drag the formula to right and downTrend FunctionFind or Trend value for 2013,2014 and 2015 by using Trend functionGo to Cell H 12 and use Trend function = =TREND($D12:$G12,$D$11:$G$11,$H$11:$J$11) & Ctrl + Shift + Enter then drag it to right and down5.3 Descriptive StatisticsInstall Data Analysis ToolPak in to Data Tab – Go to File – Options – Click Go in Add ins – Select Analysis ToolPak – Click OkSelect Data points both X and Y – B4:C15 Go to Data Tab – Select Analysis ToolPak – Select Descriptive Statistics – Select input ranges as B4:C15 then Select Output range as E4 then Click OK Presenting chart – Select Data Points B4:C15 – Insert Tab – Select Line Chart5.4 Linear RegressionGo to Data Tab – Select Analysis ToolPak – Select RegressionInput Y Range: C4:C15, Input X Range:B4:B15, Select Labels Select Output option as New WorksheetSelect All residuals and Normal Probability then Click OkRegression Output appear in the New WorksheetUse Linear Regression for One Dependent and One Independent VariableY = Mx + CIn Cell A22 = 200 as input X , Calculate Value Y in Cell B22 by using the formula = 0.6877 * A22 (200) + 1.4117 5.5 Multiple RegressionUse Multiple Regression for One Dependent Variable and More Independent VariablesY = mX1 + nX2 + CGo to Data Tab – Select Analysis ToolPak – Select RegressionInput Y Range: D4:D15, Input X Range:B4:C15, Select Labels Select Output Range as A17Select Residuals Click OKRegression Output appear in the Cell A17Regression Equation based on the Output is Price = 1.7514 + 4.8952 * Color + 3.7584 * QualitySubstitute Color Value in F38, Quality Value in G38, the Price Value appear in H385.6 Multiple Regression Finding Issues Use Multiple Regression for One Dependent Variable and More Independent VariablesY = mX1 + nX2 + CGo to Data Tab – Select Analysis ToolPak – Select RegressionInput Y Range: D5:D9, Input X Range:B5:C9, Select Labels Select Output Range as A10Select Residuals Click OKRegression Output appear in the Cell A17Regression Equation based on the Output is Price = 6 * Cars + 28 * SizeTwo Issues identified in this analysis are:The R Square and Adjusted R Square Values are Less not much nearer to Multiple R (Coefficient Correlation)The Significance F Value is Greater than 0.05 i.e. 0.1095.7 Linear Regression with Moving AverageCalculate Moving Average (MA) in Cell E7 by selecting Average of 4 period sales =AVERAGE (E7:E8) and drag the formula up to E19.Calculate Center value between Moving Average because it falls in between 3.5 & 4.5, Statistics couldn’t accept the values in decimals in Cell F7 =AVERAGE(E7:E8) drag it down up to F18Find Seasonality in Cell G7 =D7/F7 drag it down up to G18Smoothen factors of each Quarter – Copy and paste up to 5 yearsQuarter 1=AVERAGE(G5,G9,G13,G17)Quarter 2=AVERAGE(G6,G10,G14,G18)Quarter 3=AVERAGE(G7,G11,G15,G19)Quarter 4=AVERAGE(G8,G12,G16,G20)Deseasonalize the sales values in Cell I5 =D5/H5 drag it down up to I 20Before Calculating Trend – Find Regression two different data pointsFirst Calculate Regression between Period (Column A) and Sales (Column B) – Due less accuracy of data points identified through Multiple R, R2, Adjusted R2 – Calculate Regression between Period (Column A ) and Deseasonalized Sales (Column I) – Results identified that there will more accuracy in between the data points i.e. Multiple R – 95%, R2 – 92%, Adjusted R2 – 91%Calculate Trend by Period * Coefficient of Period + Intercept; =A5*$B$51+$B$50 drag the formula up to J24Find Forecast =J5*H5 and drag the formula up to K24Values appearing between K21 to K24 are forecasted Sales through this exercise.Regression between Period and SalesRegression between Period and Deseasonalized Sales ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download