Excel Tutorial PDF

DONE BY: Mshary Alharbi Excel Model 1 Microsoft Excel: is the electronic spreadsheet program an app you use to perform numeric calculations and to analyze and present numeric data. Calculations are updated automa...

DONE BY: Mshary Alharbi Excel Model 1 Microsoft Excel: is the electronic spreadsheet program an app you use to perform numeric calculations and to analyze and present numeric data. Calculations are updated automatically, so you can change entries without having to manually recalculate. worksheet: the electronic spreadsheet you work in Workbook: Worksheet contained in a file/ The file in Excel When you use Excel, you have the ability to: Enter data quickly and accurately. Recalculate data easily. Perform what-if analysis Change the appearance of information. Create charts. Share information. Build on previous work. Templates: predesigned formatted files You can use spreadsheets to: 1- Perform calculations: by Adding formulas and functions to worksheet data. 2- Represent Values graphically: by Creating charts based on worksheet data. 3- Generate reports: by combine information from multiple worksheets. 4- Organize data: by Sorting data in ascending or descending order. 5- Analyze data: by Creating data summaries and short lists using PivotTables or AutoFilters.  DONE BY: Mshary Alharbi 6- Create what-if data scenarios: by Using variable values to investigate and sample different outcomes. Name box: displays the active cell address. formula bar: allows you to enter or edit data in the worksheet. The best Use cell references when writing formulas. worksheet window: contains a grid of columns and rows. Columns are labeled alphabetically. And Rows are labeled numerically. Cell: intersection of a column and a row Every cell has its own unique location or cell address. You can specify the cell address by Column and Row intersection. cell pointer: is a dark rectangle that outlines the cell you are working in. This cell is called the active cell. Insertion point: The blinking vertical line / the flashing vertical line By default, a workbook file contains one worksheet. first worksheet, Sheet1 Sheet tab scrolling buttons: navigate to additional sheet tabs when available. Scroll bars: to move around in a worksheet that is too large to fit on the screen at once. Status bar: is located at the bottom of the Excel window. Also, It provides a brief description of the active command or task in progress.  DONE BY: Mshary Alharbi The mode indicator: in the lower-left corner of the status bar provides additional information about certain tasks. Range: selection of two or more cells or perform an action on a group of cells at once such as B5:B14 Formula: is an equation in a worksheet. Function: predefined worksheet formulas perform complex calculation -- is a built-in formula All Excel formulas begin with the equal sign (=). also called the formula prefix, While you're entering a formula in a cell, the cell references and arithmetic operators appear on the formula bar. + → Addition - → Subtraction or negative *→multiplication / → division % → percent ^ →Exponent Labels: are entries that contain text and numerical information not used in calculations, such as "2020 Traveling" or "Travel Expenses." When entering content in a worksheet, you should start by entering all the labels first. Labels help you Identify data in worksheet. Values: are numbers, formulas, and functions that can be used in calculations. Labels → Left-aligned by default Values → Right-aligned by default. Excel recognizes an entry as a value if it is a number or it begins with one of these symbols: +, -, =, @, #, $. Arguments (the information necessary to calculate an answer) Alignment: It changes the location of labels and values relative to the edges of the cell.  DONE BY: Mshary Alharbi It is important to use cell references instead of values because Excel does this on its own. AutoSum button: sums the adjacent range (that is, the cells next to the active cell) above or to the left In edit mode any time you are entering or changing the contents of a cell. Pressing: [Enter] to accept an edit moves the cell pointer down one cell. [Tab] to accept an edit moves the cell pointer one cell to the right. Or advance from one header box to the next. [F2] activates the cell for editing directly in the cell instead of the formula bar. Double-clicking a word in a cell selects it. Double-clicking the cell edits it. Calculation operators: indicate what type of calculation you want to perform on the cells, ranges, or values. Operators can include: Arithmetic operators: which perform mathematical calculations. Comparison operators: which compare values for the purpose of true/false results Text concatenation operators: which join strings of text in different cells Reference operators: which enable you to use ranges in calculations. Changing your view does not affect the contents of a worksheet.  DONE BY: Mshary Alharbi Views: Normal view: shows the worksheet without including certain details like headers and footers, or tools like rulers and a page number indicator. Normal view → Default view Page Layout view: how a worksheet will look when printed. The margins of the page are displayed, along with a text box for the header. A footer text box appears at the bottom of the page. By default, gridlines in a worksheet do not print. The header and footer is made up of three text boxes: left, center, and right. A dimmed page in Page Layout view indicates that there are No values entered Page Break Preview: displays a reduced view of each page of your worksheet You can drag the page break indicator to include more or less information on the page. Landscape: the length of the page Portrait: width of the page. Scale to Fit: options on the Page Layout tab to fit a large amount of data on a single page without making changes. Page number indicator: Tells you the current page and the total number of pages in a worksheet. Print area: the area to be printed.  DONE BY: Mshary Alharbi Model 2 Complex formula: more than one arithmetic operator. The Totals tab in the Quick Analysis tool displays commonly use Function. When you create a formula that references another cell, Excel does not record the exact cell address for the cell. Excel uses the standard order of precedence rules to determine which operation to perform first. Auto Fill: Dragging the fill handle on a cell copies the cell's contents or continues a series of data into adjacent cells. AutoFit: feature which automatically resizes the column to accommodate the widest entry in the column. Function always begins with an equal sign (=) as the formula prefix. The AVERAGE function is a statistical function. Formula AutoComplete: feature makes it easier to enter function names by typing. This feature minimizes the Amount of typing you need to do to enter a function and reduces typing and syntax errors. There are three ways you can copy or move cells and ranges from one (worksheet – workbook – Microsoft program) to another: 1- the Cut, Copy, and Paste buttons on the Home tab. 2- the fill handle in the lower-right corner of the active cell or range 3- the drag-and-drop feature.  DONE BY: Mshary Alharbi When you are pasting, you need to specify the Upper Left cell of the range where you want to paste the selection. Clipboard: is a temporary Windows storage area that holds the selections you copy or cut. the Office Clipboard contains up to 24 There are 2 types of Cell Reference: 1- Relative reference: Is the default Reference in Excel. For example: A1 or B1*A15 Uses when you want to preserve the relationship to the formula location. Excel recording the input cells in relation to or relative to the formula cell. 2- Absolute Cell Reference: For example: $A$1 or $C$3*C$5$ Use absolute cell references when you want to preserve the exact cell address in a formula. You create an absolute cell reference by placing a $ dollar sign) in front of both the column letter and the row number of the cell address. Press [F4] the dollar signs ($) are added automatically. Round: result to a specific number of decimal places. 0 indicates that you don't want any decimal places to appear in the calculated value. A moving border surrounds the selected range until you press {Esc} When the parentheses close and become bold briefly, this indicates a formula is correct. ###### in cell → column is too narrow to display the value completely #Name? in cell → formula contains an error  DONE BY: Mshary Alharbi Model 3 The default accounting number format adds dollar signs and two decimal places to the data. The Accounting and Currency number formats are both used for monetary values, but the accounting format aligns currency symbols and decimal points of numbers in a column. Font: is the name for a collection of characters (letters, numbers, symbols, and punctuation marks) with a similar, specific design Font size: is the physical size of the text, measured in units called points. A point is equal to 1/72 of an inch. The default font and font size in Excel is 11-point Calibri. Font styles: are formats you can apply to affect the way text and numbers look in a worksheet. You can also change the alignment of labels and values in cells to position them in relation to the cells' edges. Merge & Center button: Centers text and combines two or more selected, adjacent cells into one cell. Wrap Text Button: Wraps long text into multiple lines in the cell. -When you insert a new column. the column is inserted to the left of the cell pointer and the contents of the right. -When you insert a new row, the row is inserted above the cell pointer and the and the contents of the worksheet shift down The default column width is 8.43 characters, a little less than 1 inch. Worksheet names can have up to 31 characters, including spaces and punctuation. You can copy cell formatting using Format painter button.  DONE BY: Mshary Alharbi Column heading: is the box at the top of each column containing a letter. When you change the column size in normal view it appears as Characters, but in Page Layout view mode it appears as Inches. When you want to insert multiple columns, you should select the same number of columns you want to insert. When you delete or add a row or column, the formulas in the cells will NOT change. You can change the names of worksheets in Excel at any time. Excel includes a built-in dictionary and spell checker. Excel does not check the spelling of the entire workbook but rather each worksheet individually. Pressing [Delete] on the keyboard removes only the contents of a selected row or column. You can move multiple sheets by pressing and holding [Ctrl] while clicking the sheets you want to move.  DONE BY: Mshary Alharbi Model 4 Chart: is an object, or an independent element on a worksheet, and is not located in a specific cell or range When a chart is selected in Excel, the Name box tells you the chart number. A chart sheet: is a sheet in a workbook that contains only a chart that is linked to the workbook data. Embedded chart: is inserted directly in the current worksheet and doesn't exist in a separate file. Embedding a chart in the current sheet is the default selection when creating a chart. Major gridlines: represent the values at the value axis tick marks. Minor gridlines: represent the values between the tick marks. Data series: A collection of related data points in a chart Legend: The object in a chart that identifies the colors used for each data series. Format tab and Design tab appears when a chart is selected. Insert tab on the Ribbon do you use to create a chart. Sizing handles: small series of dots at the corners and sides of the chart's border. When you select a chart, a blue border surrounds the cells containing the data on which the chart is based. When you select a chart, a Purple border surrounds the cells containing the category axis labels. When you select a chart, a red border surrounds the cells containing the data series labels.  DONE BY: Mshary Alharbi Pressing and holding the [Shift] key holds the vertical horizontal position of the item as you move it. You can turn of the Format Painter by pressing (Esc) or (On its button again) Diagonal resizing: Change chart shape form corners. Quick Layout offer arrangements of objects in your chart.