310 likes | 672 Views
EXCEL ADVANCED. Mathematical Operators for Excel. < > = >= <= <> ^. Less than greater than Equal Greater than or equal Less than or equal Not equal Power of. Functions. SUMIFS Adds the cells in a range that meet multiple criteria. COUNTIFS
E N D
Mathematical Operators for Excel • < • > • = • >= • <= • <> • ^ • Less than • greater than • Equal • Greater than or equal • Less than or equal • Not equal • Power of
Functions • SUMIFS • Adds the cells in a range that meet multiple criteria • COUNTIFS • Applies criteria to cells across multiple ranges and counts the number of times all criteria are met The key difference between these and Countif/Sumif is that these allow the use of multiple criteria. Countif/Sumif do not
DATA TABLES • A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas • To create a Data Table select data and click Insert tab, Table (in table group) • Convert a table to a range of data Click anywhere in the table, click on Design tab then click Convert toRange in Tools group.
DATA TABLES • Can be used to Calculate Options • In example sheet in cell J2 type =G3 then select I2:J15 • Click Data tab, What-if-analysis,then Data Table • In Data Table, Column inputcell, click D4, and click OK
Protecting Worksheets • Two step process, first unlock cells you want user to change • Select cells you want unlocked • Home tab, Font group, click on Dialogue Box expander, click on Protection tab, and remove check mark from “Locked” choice
PROTECT SHEETS • REVIEWtab > CHANGESgroup > PROTECT • SHEET button • select the options you want to be protected > OK
APPLY CONDITIONAL FORMATTING WITH A RULE • Select cell range • HOMEtab > STYLESgroup > CONDITIONAL FORMATTING > NEW RULE
CONDITIONAL FORMATTING WITH A RULE cont. • Select a RULE TYPE: • Set your parameters: • Select the formatting you want by clicking on the button at the bottom
SORT BY MULTIPLE FIELDS • HOMEtab > EDITINGgroup > SORT & FILTERButton > CUSTOM SORT • For each category you want to sort by, click on the ADD LEVEL button
AUTOFILTER • Select a range of cells containing data. • HOMEtab > EDITINGgroup > SORT & FILTERbutton > FILTER • Drop-down arrows will now Appear beside each Column heading • Select the drop-down arrow and: • De-select: SELECT ALL • Then select the checkbox beside the option you wish to sort by
SUBTOTALS • DATA tab > • Note that data should be sorted to get best results • You can automatically calculate subtotals and grand totalsin a list for a column by using the Subtotal command in theOutlinegroup on the Data tab.
PIVOT TABLE • Are used to summarize, analyze, explore, and present summary data • Select the range • INSERT > click on PivotTable • My table has headers is selected > OK
Modify A PivotTable So That A Column Displays The MAXIMUM Value, Instead Of The SUM • Select the cell which has the desired COLUMN HEADING • OPTIONS tab > ACTIVE FIELD group > FIELD SETTINGS button • In the list, select the Desired function > OK
PIVOT TABLE • Drag the fields you want into the areas you want
PIVOT TABLE cont. • Format a PivotTable using a Pivot style • Click the DESIGNtab: • Light styles • Medium styles
PIVOT CHART BASED ON A PIVOT TABLE • PIVOT TABLE TOOLS > OPTIONS > TOOLS group > PivotChart button • in the PivotChart Filter Pane which pops up when you create the PivotChart • Click on the drop-down arrow beside the 1st category name • De-select: SELECT ALL • Then select the categories you want to be Able to view in your PivotChart > OK
GOAL SEEK • Automatically vary the contents of one cell • so that the value of the contents of another cell equals a certain amount • Click DATAtab > DATA TOOLS group > "WHAT-IF ANALYSIS" icon > GOAL SEEK • In the SET CELL textbox, key in the cell you want the ANSWER to appear in • In the BY CHANGING CELL textbox, key in the cell reference you want changed in order to get the desired answer > OK