Everything About Advance Excel

data_validation goal macros subtotal pivote vlookup consolidate text_to_columns conditional ole chart data_filterning manymore

Other Topics


OTHER IMPORTANT TOPICS IN ADVANCE EXCEL 

  • Consolidation : this command is from data tab .consolidate worksheets located in one workbook or multiple workbooks and take it into one worksheet.consolidate more than one worksheets (located in same workbook or in multiple workbooks).apply the function like sum,average,product,while using consolidation but do not forget to select links to source data while using consolidation. 3D formula is same like as consolidation but it refers to the same cell or range of cells on multiple worksheet. 3D formula =sum(sheet1:sheet 4!A2).sum/add number is cell A2 on sheet 1 , sheet 2, sheet 3, sheet 4 
  • Goal Seek : this command is from data tab under what-if-analysis.goal seek feature use to find the input values needed to achieve the desired goal.suppose in cell A4 I have written 50 and in cell A5  60 and in A6 I made the sum of both A4+A5.Here the figure which is cell A4 as well as in A5 called as a cell value and the answer which I got using formula in cell A6 called as a target value.Now suppose I want to change my target value to 100. To get this 100 answer        ( target value).I have to use here goal seek function. Goal seek you can also apply for any tasks like calculating EMI in banks/financial institutions. 
  • Date Table : this command is from data tab under data tools group  in  what-if-analysis.This function is used to create long automated tables. 
  • Advance Filter : this command is from data tab.helps to extract information from your data using single or multiple criteria. The function is available in data tab under sort and filter.use criteria sign > < = before using this function.list range is your selected data range and criteria range is the range where you can apply > < = sign to get the result.
  • Subtotal : this command is from data tab. It quickly calculate rows of related data by inserting  subtotals and their grand totals. To take print of subtotal on each page separably make tick in page break between groups check box.The subtotal function is available in data tab under outline group.while using subtotal select as each change in properly according to your requirement.   
  • Auto Outline/Clear Auto outline : this command is from data tab. use to temporarily hide row or column of data & to clear use clear auto outline.For Auto Outline, the data must already contain with formulas  
  • Get External Data : this command is from data tab .we get external data in excel by using different ways i.e from access,web,text document, from other sources and from existing connection.We can easily format the data as per requirement after taking it from other sources  
  • Connections : use to create and edit connections to external data sources that are stored in a workbook or in a connection file. used to connect more than one workbooks together.The function is available in data tab under connection group.you can add/remove/refresh more than one workbook at a time using connections.But to check result of all added workbooks in current worksheet select get external data in data tab selecting existing connections option  
  • List command in Data validation : use to create source box. use to create drop down list like gender list Male/Female.List command is available in data tab under data validation in settings. 
  • Text To Columns :To separate the contents of one Excel cell into separate columns.the function is available in data tab under data tools group        
  • Macros :A macro is a piece of programming code that runs in excel to help automatic routine tasks.Macros function is available in view tab under macro group.Take a blank sheet and start recording macros. stop it after recording and to check/edit go to view macros from where you can run or edit or delete  your created macros.
  • Absolute References :Do not change when copied or filled. it is used to keep a row and/or column constant. it is designated in a formula by the addition of a $ sign and F4 is the key function to bring that $ sign.
  • Relative References :Unlike absolute references, relative references change when copied or filled. it is normal formulas copying without using $ sign.To apply the formula straight vertically and straight horizontally use relative references 
  • Mixed References :Is either an absolute column and relative row or absolute row and relative column.you can get absolute references by adding $ sign before column letter/row number.but for relative references no $ sign.(use in formulas) 
  • Transpose : it converts a vertical range of cells to a horizontal range and horizontal range to a vertical range.Transpose is available in home tab under clipboard group in paste drop down list.first copy the required range of table then select the range area where you want to make it vertical/horizontal and then apply this transpose command. 
  • Pivot Table :Easily arrange and summarize complex data in a pivot table. extract the significance from a large,detailed data.It is available in insert tab under table group. you can easily drag fields between areas like column and rows according to your requirement.and can choose only required field add to report 
     
  • Lookup Formula : use to search one row or one column.
  • Sumif : add the cells specified by a given condition/criteria.can help us perform powerful calculations and analysis of data.
  • Object Link Embedding :OLE is a technique used to insert data from one program into another.This function is available in insert tab in object.Just click on object and select create from file browse the file and select it but do not forget to select link to file(it makes link of selected file/program) 
  • Protect Cells : you can lock cells if you want to protect cells from being edited.Before applying this function you should select entire sheet first and to remove cell lock of entire sheet go to font/alignment/number group and under protection clear locked check box.Then come to review tab and select protect sheet and insert the password & confirm it again.this way you can protect only the selected cell/range of cells.  
  • PMT formula : one of the best financial function calculate the payment for a loan based on constant payments and a constant interest rate.Used to calculate EMI in financial institutions/banks 
  • Auditing :It is having two function Trace Dependents and Trance Precedents. Trace precedents show arrows that indicate which cells affect the value of the currently selected cells. and Trace dependents show arrows that indicate which cells are affected by the  value of the currently selected cell.After auditing click on remove arrows.These two functions are available in formula tab under formula auditing group  
                                                                                                 
                 
                                                                     

                                                        





Subscribe to: Posts (Atom)