* The Welcome Page * Welcome tour * Recently opened workbooks * Create blank workbook or choose from templates. = The Ribbon * Toolbar at the top of the screen, organized into tabs, each with command groups. + Tabs: groups of related functionality. eg: Home includes frequent commands. Insert has commands for inserting objects into the work area. * Groups: groups related commands. eg: Fonts contains commands related to fonts + Commands: specific commands inside a group. eg: Under Fonts are commands for changing font type, size, color, etc. BUSINESS | TAXATION | ACCOUNTING | LAWMicrosoft Excel 2016 Interface * Tell me ... Search bar to quickly find commands for use. "Backstage view - Options for Saving, opening, printing, sharing workbooks. * Access from the File tab on the Ribbon. * Workbooks - Excel files are called workbooks * Create a new workbook (either blank workbook or from a template), or open an existing workbook. * Select “Blank Workbook” Anew blank workbook with a default name is created, Change the name via File | Save As | Rename + The workbook is saved to OneDrive, Microsoft’s online storage to save, edit, and share documents CTU VCC) Cotete Ee ean~ undo last change CMD/CTRL + C - copy selected CMDICTRL +V - Paste selected MULTI-SELECT - hold Shift key (Mac) or CTRL (Windows selecting, CMD/CTRL + F - find values CMDICTRL + G- g0 to callsExcel Basics * Worksheet: Grid work area Rename double-click Cells: Worksheets contain many recta * Columns: Identified by letters + Rows: Identified by numbers Cell Address: Cells are referenced by column and row A5 corresponds to column A. row 5 Cell Range: Group of cells instead of a single cell address * Select a range by selecting multiple cells + Cell ranges includes addresses of first and last cells separated by a colon. eg: A1:A5 includes cells, Al. A. A1:D1 includes cells. Al, B1,C1.D1 7 BUSINESS | TAXATION | ACCt INGExcel Basics = Cell Content * Cells contain different content types: letters, numbers, dates, formatting, formulas, and functions. Cell Formatting - changes the way letters, numbers, and dates are displayed. eg: percentages appear as 0.15 or 15% ; prefixed monetary values with $ Cell Formulas - used to calculate cell values. Begins with an equal (=) sign. eg: =(A1+A2)*A3 + Note: ' * (*) asterisk used for multiplication. : * * for exponents 8 BUSINESS | TAXATION | ACCOUNTING |Cell Functions * Many pre-built fimetions to make calculations easier. Functions inelude a name and. p; Arguments or mputs. eg: SUM(B3:B9) adds all the values in cells BS through B7 airentheses with Here the SUM tunetion * Insert Function £ = Filter on the vategory of functions such as Commonly Used. Statistical. ele. BUSINESS | TAXATION ec me]Excel Basics = Cell Functions * Many pre-built functions to make calculations easier. Functions include a name and parentheses with arguments or inputs. eg: = SUM(B3:B9) * Here the SUM function adds all the values in cells B3 through B7 » Insert Function * a Filter on the category of functions such as . Commonly Used, Statistical,Excel Basics « Fill handle: For copying cell content to adjacent cells in the same row or column " * Provides a quick way to copy content, especially useful for formula content * Select content cell(s) then hover over the cell’s lower-right corner so the fill handle appears and drag until all desired cells are selected. Release the mouse to fill the selected cells c D E == pleC TOTAL 3] él 7 + 9 rTFill handle: * Formula & Function content: * Ifthe cell content contains a formula or function, these are - copied to the adjacent cells, and the cell references are automatically adjusted relative to new location c D E pleC TOTAL 3 6, 6 + 9* Modify column width * Position the mouse over the column line. The cursor becomes a double arrow. Click and drag the mouse to increase or decrease its width. M22 “@ A cS c Monthly Budget OhwneExcel Basics : AutoFit column width: sets a column's width to automatically fit its content + Position the mouse over the line in the column heading. The cursor becomes a double arrow. Double-click and the column width is changed to fit the content. ¢ AutoFit multiple columns. Select columns to AutoFit. * Double click on the double arrow. You can drag to manually adjust the width Drag to resize multiple columns A 8 ae sweExcel Basics ) " Modify Row Height: * Position the cursor over the row linc. The cursor becomes a double arrow. Click and drag the mouse to increase or decrease its height. a to resize rnultiple rowsExcel Basics * Modify all rows or columns: modify the height and width of every row and column Select all cells via Select All or select required cells Row Height: Position the mouse over a row line then drag the mouse to increase or decrease the row height Column Width: Position the mouse over a column line then drag the mouse to increase or decrease the column width A 8 ce efbeN ® WSs to resize multiple rowsExcel Basics = Insert rows * Select the row below where robat a new row is to be inserted. The new row will be nn ae inserted above this row ecetcncames . * Click Insert command from ent het Clas Home tab so cts ds or right click, select Insert a TELExcel Basics * right click, select Insert Clear Contents format Celis. Bow Heoht Hice unniae} tle * Insert columns_ Excel Basics Select the column to. the tight of where the new column is to be inserted. The new column is inserted to the left of this column. Click Home, Insert or right click, Insert Columns “ce Gailibi <1 + A AE BIS¢-A-E Francis err X cut TR copy [Paste Options: q Insert ? Delete Clear Contents ox ca EE] Eormat ceits. Row Height. Hide Unhide* Copy & Paste Cell Content: Copy content from existing cells to other cells + Select cell(s) to copy. From Home, Copy, or Ctrl+C keyboard shortcut. Copied cell(s) are surrounded by dashes. * Select cell(s) to paste. From Home, Paste, or Ctrl+V keyboard shortcu * Press ESC to undo dashed lines * Cut & Paste cell content: Unlike copy and paste that duplicates content, cutting moves content between cells. * Select cells to cut. Right-click, Cut, or Home,Cut or Ctrl+X shortcut * Select cells to paste the content. Right-click, Paste, or Home, Paste or Ctrl+V shortcut key= Delete (or clear) cell content + Select content cell(s) to delete. + From Home, Clear command, Clear Contents, or Delete key = Delete a Row * Select the row to delete + Right-click, Delete Sheet Rows OR Home, Cells, Delete Sheet Rows + The selected row is deleted and those around it shift = Delete a Column * Select the column to delete + Right-click, Delete Columns OR Home, Cells, Delete Sheet Columns + The selected column is deleted and those around it shift.* Wrapping Text: When cell width is not wide enough to fit its contents «Select the cells to wrap. Can be a single cell or multiples * Click the Wrap Text from Home. Or search ‘wrap’ from “Tell me..” * Select Wrap Text again to remove wrapping = Cell Formatting: changes the way letters, numbers, and dates are displayed. For example: percentages appear as 0.15 or 15% OR prefixed monetary values with $ «Select cells to format. + From Home, Number tab, sclect formatting desired. Choose from formatting categories. ‘More number formats..’ allows you to testCONTENT = Common data types ———— = Number Formatting Basics —~ = Number Formatting Practice4) 3 common types of data used in spreadsheets applications 1. Text: 2. Number: 3. Formulas:EXCEL DATA TYPES Text: Also called labels. Used for headings and names to identify columns of data. Can contain letters, numbers, and special characters IS)EXCEL DATA TYPES Number: Also called values. Used in calculations. Aside from actual numbers, Excel stores dates and times as numbers. hen numbers are formatted as text * Problems happen Ww! from being used in calculations. data, preventing them a ISEXCEL DATA TYPES Formulas: mathematical equations on combination of data. Functions are formulas that are built into Excel.Number Formats: Number Format Specifies the data type to be crew’ omer used. Eg: percentages (%), om au currency ($), times, dates, vo recente ete Seam * Tells Excel the types of Se ‘oxo (eaten values stored in a cell — =e = Ensures your data is consistent so formulas are calculated correctly = Excel applies a lot of Sarat oma steel tee aca te coc regina dae number formatting automatically based on ox how data is entered* Using number formats: . Home, Number group, drop-down menu, and sclect the desired format from a category OR Quick number-formatting commands Currency format: adds symbol (eg: $) with two decimal places for numerical values Actual Value: Select the cell to see the actual value used by Excel in formulas and other calculations B2 hn A 8 c 1 umber 2 2i[_$21.001 3 33 $33.00 4 16 $16.00= Percentage formats: displays values as percentages, Eg: 50% ¢ Typing (%) after a number, the percentage number format is te i i Excel automatically applied by Excc! = of czas a:A 8 c 1 Product Price Sale Tax $21.00[__$1.05] 2 [Jacket 3 Tax Rate 5.00% 4 « Without the %, Excel uses a - fe nen general formatting and docs not aA B 7 cl 1 [product Price —_ Sale Tax calculate tax correctly 1 {i 2 og SD 3. TaxRate 5 oT- Date formats: When working with dates, a date format is required to tell Excel the specific calendar dates. Eg: June 18, 2020 * Allows use of date functions that use time and date to calculate date information * Requires a specific format so Excel understands the value is di nth/year depending on which country late. Example: month/day/year or day/mol you're in Date i, _ a 8/2020 - t Valid formats 18, = Can usc the fill handle to continuc dates through the column. Excel popu one day a RS lates cach successive cell byNumber Formatting Basics Text Format: when you want data to appear exactly as entered od for numbers not to be used in calculations. Eg: phone number, zip codes = Increase/ Decrease Decimal Place value « Controls number of decimal places displayed in a cell for float/decimal numbers * Doesn't change the cell's actual value, only the display « Decreasing the decimal displays the value rounded to that decimal place - Number formatting tips « Apply number formatting to al ~ When applying number format values to ensure correct formatting ¢ column for a given data type in entir double-check tting to existing values, EeNumber Formatting Practice 1. Enter these dates to see if the date format is automatically applied by Excel - ¢ 10/12 + October * October 12 « October 2020 = 10/12/2020 « October 12, 2020 « 2020 « October 12th a TNCONTENT Relative and Absolute Cells * Working Across Multiple Sheets « Sorting Data * Filtering DataWorking with Data There are 2 types of cell references: Relative and Absolute Relative cell references in formulas automatically change when copied to adjacent cells based on their relative location Absolute references do not change when copied. Use an absolute reference to keep a row and/or column constant. + Are prefixed with dollar sign (S) before the column and row. Mixed reference. When $ precedes a column or row, but not both SRL te eee CO go ie nee] UOTE ge en eee= Working Across Multiple Sheets: Excel allows you to refer to any cell on any worksheet ¢ with the worksheet name followed by an + Begin the cell reference heet |, its cell exclamation point (!) Example: to reference cell Al on SI reference would be Sheet] !A1 * Note: if a worksheet name contains a space, you need to include single ions ("') around the name. Example: To reference cell Al ona quotat 1] reference would be: ‘Menu Order"tAl worksheet Menu Order, its ce! aWorking with Data = Sorting Data: Allows you to organize a worksheet by sorting your” data. Example: organizing a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways = Sort sheet: organizes data in * Select worksheet: t-shirt orders We're going to sorta t-shirt orders alp! a worksheet by one column. habetically by Last Name. + Select cell C2. , From the Data tab on the Ribbon, click the A-Z command to sort in A command to sort descending. ascending order, or Z- omen)Sort sheet: organize: Sorting Data: Allows you to organize a worksheet by sorting your data. Example: organizing a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways ¢ data in a worksheet by one column. * Select workshect: t-shirt orders We're going to sorta t-shirt orders alphabetically by Last Name. = Select cell C2. From the Data tab on ascending order, oF Z- _ the Ribbon, click the A-Z command to sort in Acommand to sort descending.Hea ratte Rast Sort range: sorts the data in a range of cells. Will not affect other content on the worksheet \ + Select the table Total Orders (by Grade) + Select the cell range to sort. G2:H6 * You can multi-select (SHIFT + arrows) or used Goto Dialog (CTRL/CMD + G) and entering the range. + Select the Data tab, then the Sort command. The Sort dialog appears. * Choose the column you to sort by. Select Orders. + Select the sorting order (either ascending or descending). * Select Descending to sort from largest to smallest. * Select OK BUSINESS | TAXATION | ACCOUNTING | LAW‘evels= allows you to a data by more than one column * Select worksheet t-shirt orders * Select A2 then click the Data tab, and the Sort command.The Sort dialog box appears. * Select the first column to sort by. We will sort by Homeroom# Order: Sort Descending * Click Add to add another column (add level) to sort by. Select Last Name Order: Sort Ascending The table is now sorted, first by Homeroom, from largest to smallest then by Last Name, alphabetically. Click the Up or Down arrows to change the order of the sorting levels. BUSINESS | TAXATION | ACCOUNTING | LAW rtWorking with Data - Filteri 2 Fi tering Data: Filters are used to narrow down data in a worksheet so ‘you ean view only the information you need = For filtering to work, the worksheet must include a header row, used to identify the name of each column Select worksheet: equipment checkout. Select the header row Select the Data tab, then th in the header cell for each column. Select dropdown on column B to view only certain types Uncheck Select All to Check Laptop and TV to vi a TESTIS) ¢ Filter command. A drop-down arrow appears 5 of equipment. desclcct all data. ew only these types of equipmentWorking with Data Applying multiple filters: Filters are cumulative, meaning you can apply multiple filters to further narrow your results. We've already filtered the worksheet for laptops and TVs. We can further narrow the results to show only laptops and TVs checked out in August. * Click the drop-down arrow for column D. Uncheck all dates and select August. * The worksheet is now filtered to show only laptops and TVs checked out in August. Clearing a filter * Click the drop-down arrow for the filter to clear. Sclect column D. * The Filter menu appears.Choose Clear Filter From [COLUMN NAME] To remove all filters, click the Filter command on the Data tab.Working with Data Advanced filtering: Excel includes many advanced filtering tools, including search, text, date, and number filtering Advanced text filters: used to display more specific information, like cells that contain a specific word Enable filtering by sclection Data tab, Filter command. Click the drop-down arrow for column C Sclect Text Filter, Does Not Contain. In the Custom AutoFilter dialog, enter laptop to exclude items containing the word laptop. The worksheet is displays only items meeting the filter Clear all previous filters by clicking the Filter command under Data tabWorking with Data - Advanced number filters: allows defining custom filters for numbered data « Enable Filter again then select drown-down arrow on Column A * Sclect Number Filters. Choose Between to view ID numbers between a specific range. « Inthe Custom AutoFilter dialog, Enter 300 3000-6000 range «Clear all previous filters by clicking the Filter command date filters: used to view data from a certain time period hen sclect drown: Choose Between to view equi) 10 to 6000, to display IDs in the under Data tab anced > Enable Filter again t « Select Date Filters. between a range of dates ustom AutoFilter dialog, enter TAS/S and 8/15/15. + IntheC 1 You can sort Ascen ing order to make it easier to view _down arrow on Column D pment that checked oul