Microsoft Excel Tutorial PDF

Chapter 5: REVIEW ON PRODUCTIVITY TOOLS MICROSOFT EXCEL MICROSOFT EXCEL PART 1 – Overview of Microsoft Excel What is Spreadsheet and MS Excel? A spreadsheet is a sheet of paper that shows accounting or other data in rows and columns;...

Chapter 5: REVIEW ON PRODUCTIVITY TOOLS MICROSOFT EXCEL MICROSOFT EXCEL PART 1 – Overview of Microsoft Excel What is Spreadsheet and MS Excel? A spreadsheet is a sheet of paper that shows accounting or other data in rows and columns; a spreadsheet is also a computer application program that simulates a physical spreadsheet by capturing, displaying, and manipulating data arranged in rows and columns. Uses of Spreadsheet Program A program that allows you to use data to forecast, manage, predict, and present information. Microsoft Excel is the spreadsheet component of the M icrosoft Office Suite. It is used primarily to enter, edit, format, sort, perform mathematical computations, save, retrieve and print numeric data. Spread programs like MS Excel are tools that helps day -to-day transactions of organizations easier and more organized. The following are sample reports and documents that could be prepared and managed using M S Excel: Schools – Students grades, payroll, forms Sports – Individual and Team Statistics Personal – Checkbooks, household expenses Business – payroll, investments, accounting reports Offices – Forms, reports, data presentation Excel Window Parts and Functions M S Excel Screen Elements and their Functions Name Description Active cell indicator This dark outline indicates the currently active cell (one of the 17,179,869,184 cells on each worksheet). Collapse the Click this button to temporarily hide the Ribbon button Ribbon. Click it again to make the Ribbon remain visible. Column letters Letters range from A to XFD — one for each of the 16,384 columns in the worksheet. You can click a column heading to select an entire column of cells or drag a column border to change its width. File button Click this button to open Backstage view, which contains many options for working with your document (including printing) and setting Excel options. Formula bar When you enter information or formulas into a cell, it appears in this bar. Horizontal scrollbar Use this tool to scroll the sheet horizontally. Macro recorder Click to start recording a Visual Basic for indicator Applications (VBA) macro. The icon changes while your actions are being recorded. Click again to stop recording. Name box This box displays the active cell address or the name of the selected cell, range, or object. New Sheet button Add a new worksheet by clicking the New Sheet button (which is displayed after the last sheet tab). Page View buttons Click these buttons to change the way the worksheet is displayed. Quick Access This customizable toolbar holds commonly toolbar used commands. The Quick Access toolbar is always visible, regardless of which tab is selected. Ribbon This is the main location for Excel commands. Clicking an item in the tab list changes the Ribbon that is displayed. Tell me what you Use this control to identify commands or have want to do Excel issue a command automatically. User name The name (and associated image) of the person logged in. Ribbon Display A drop-down control that offers three options Options related to displaying the Ribbon. Row numbers Numbers range from 1 to 1,048,576 — one for each row in the worksheet. You can click a row number to select an entire row of cells. Sheet tabs Each of these notebook-like tabs represents a different sheet in the workbook. A workbook can have any number of sheets, and each sheet has its name displayed in a sheet tab. Sheet tab scroll Use these buttons to scroll the sheet tabs to buttons display tabs that aren't visible. You can also right-click to get a list of sheets. Status bar This bar displays various messages as well as the status of the Num Lock, Caps Lock, and Scroll Lock keys on your keyboard. It also shows summary information about the range of cells selected. Right-click the status bar to change the information displayed. Tab list Use these commands to display a different Ribbon, similar to a menu. Title bar This displays the name of the program and the name of the current workbook. It also holds the Quick Access toolbar (on the left) and some control buttons that you can use to modify the window (on the right). Vertical scrollbar Use this to scroll the sheet vertically. Window Close Click this button to close the active workbook button window. Window Click this button to increase the workbook Maximize/Restore window's size to fill the entire screen. If the button window is already maximized, clicking this button “unmaximizes” Excel's window so that it no longer fills the entire screen. Window Minimize Click this button to minimize the workbook button window. The window displays as an icon in the Windows taskbar. Zoom control Use this to zoom your worksheet in and out.  Creating New File/Workbook When MS Excel is loaded for the first time: 1. Click Blank Workbook option.. When MS Excel is already loaded 1. Click File Tab, Click New menu then click Blank Workbook option. Saving File/Workbook Saving workbook for the first time or saving it to another location/folder 1. Click File tab, click Save/Save As or press CTRL+S/F12. 2. Choose Location/Folder (Documents) by clicking Browse menu. Take note: It is recommended to create your own folder to make your files organized and easy to manage. 3. Type file name. Click save. Saving existing workbook 1. Click File tab, click Save or press CTRL+S. Take note: This is used when saving an updated content of an existing workbook. Closing Workbook 1. Click File tab, then click Close menu of press CTRL+W Opening an Existing Workbook When MS Excel 2016 is loaded for first time 1. Click desired file on the Recent file list; or 2. Click Open Other Workbooks menu,. 3. Locate your file, then click Open button. When MS Excel 2016 is already loaded 1. Click File tab, then click Browse button,. 2. Locate your file, then click Open button. Using File Explorer 1. Click File Explorer button on the task bar, or press CTRL+E. 2. Locate your file then double-click on it. Exiting MS Excel 2016 Method 1: Click File tab, then Exit or press Alt +F4. Method 2: Click Exit button ( ) on the upper right corner of your Window. PART 2 – Worksheets and Ribbon Tabs Excel worksheet components A worksheet is a single page or working area in excel where data put in, stored and manipulated in a table-like environment consisting columns and rows. MS Excel 2016 worksheet include 1,048,576 rows; 16, 384 columns; and 17,179,869,184 cells.  Figure 2. M S Excel 2016 worksheet The formula bar is located below the Ribbon. This area displays the contents of the active cell. It can also be used for entering or editing data and formulas. Located to the left of the formula bar, the insert icon dialog box helps the user identify and implement functions, a type of formula that performs specialized and group calculations. Located to the left of the formula bar, the name box displays the cell reference or the name of the active cell. A worksheet is a single page in an Excel workbook. By default, there is one worksheet in an Excel 2016 file. The tab at the bottom of a worksheet tells you the name of the worksheet (e.g., Sheet1, Sheet2, etc.). Renaming a worksheet or changing the tab color can make it easier to keep track of data in large spreadsheet files. Adding additional worksheets can be done by clicking on the add sheet icon next to the sheet tab at the bottom of the screen or you can use press Shift+F11 to add a new worksheet. Switching between worksheets can be done by clicking on the tab of the sheet you wish to access or by using this keyboard shortcut to change between worksheets. Each worksheet is divided into rows and columns. Rows run horizontally in a worksheet and are identified by numbers in the row header. Columns run vertically and are identified by letters in the column header. A cell is the intersection point of a vertical column and a horizontal row. Data entered in a worksheet is stored in a cell. Each cell can hold only one piece of data at a time. Each cell in the worksheet can be identified by a cell reference, which is a combination of letters and numbers such as A1, F456, or AA34. The active cell is recognized by its black outline. Data is always entered in the active cell. Different cells can be made active by clicking on them with the mouse or by using the arrow keys on the keyboard. The status bar runs horizontally along the bottom of screen and can be customized to display several options, which give the user information about the current worksheet, data the worksheet contains, and even the user's keyboard - such as whether the Caps Lock, Scroll Lock, and Num Lock keys are turned on or off.  Located in the bottom right corner of the Excel screen, the zoom slider is used to change the magnification of a worksheet by dragging the slider box back and forth or by clicking on the Zoom Out and Zoom In buttons located at either end of the slider. The MS Excel Ribbon Tab As noted in the picture below, Ribbons are designed to help you quickly find the command that you want to execute in Excel 2016. Ribbons are divided into logical groups called Tabs and Each tab has its own set of unique function to perform. There are various tabs – Home, Insert, Page Layout, Formulas, Date, Review and View. Figure 3. The Ribbon File Tab This contains commands and options that are associated with the file such as file information, details, recent files and excel options. The file tab also contains the commonly used commands such as Save, Save As, Open, Close, New, Print and Exit. Figure 4. The File Tab Home Tab contains all the commands that are commonly used like formatting, alignment, inserting and deleting rows or columns, sorting and filtering. Figure 5. Home tab  Insert Tab Insert tab contain commands most commonly associated with adding something to the workbook. Figure 6. The Insert Tab Page Layout Tab Page Layout tab contains the commands most commonly associated with settings that would affect the entire page or worksheet. Figure 7. Page Layout Formulas Tab The Formulas tab contain the commands most commonly associated with Excel’s formulas and functions. Figure 8. Formulas Tab Data Tab The Data Tab contains the commands most commonly associated with data from external sources, and evaluating or arranging data. Figure 9. Data Tab Review Tab The Review Tab contains the commands most commonly associated with the text portions of a spreadsheet and sharing changes.  Figure 10. Review Tab View Tab The View Tab contain the commands most commonly associated with the variety of ways you can “look at” a worksheet or workbook. Worksheet tabs and views A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets. Select a Worksheet When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window. Figure 12. Worksheet in a tab Insert a Worksheet You can insert as many worksheets as you want. To quickly insert a new worksheet, click the plus sign at the bottom of the document window.  Result: Rename a Worksheet To give a worksheet a more specific name, execute the following steps. 1. Right click on the sheet tab of Sheet1. 2. Choose Rename. 3. For example, type Sales 2016. Move a Worksheet To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position. 1. For example, click on the sheet tab of Sheet2 and drag it before Sales 2016.  Result: Delete a Worksheet To delete a worksheet, right click on a sheet tab and choose Delete. 1. For example, delete Sheet2. Result: Copy a Worksheet Imagine, you have got the sales for 2016 ready and want to create the exact same sheet for 2017, but with different data. You can recreate the worksheet, but this is time-consuming. It's a lot easier to copy the entire worksheet and only change the numbers. 1. Right click on the sheet tab of Sales 2016. 2. Choose M ove or Copy. The 'Move or Copy' dialog box appears. 3. Select (move to end) and check Create a copy. 4. Click OK. Result: Note: you can even copy a worksheet to another Excel workbook by selecting the specific workbook from the drop-down list (see the dialog box shown earlier). MS Excel Vocabularies 1. Active Cell – The cell in your worksheet that has been selected. It will have bolder gridlines around it. 2. AutoFill – A feature that allows you to quickly apply the contents of one cell to another cell or range of cells selected. 3. AutoSum – A function that automatically identifies and adds ranges of cells in your worksheet. 4. Cell – The rectangular shaped area on a worksheet that is created by the intersection of columns and rows. 5. Cell Address – The name of the cell is determined by the name of the row and the column intersecting, such as A8. 6. Cell Grid – The lines on your worksheet that separate the columns and rows. 7. Column – In a worksheet, the vertical spaces with headings A, B, C, and so on. 8. Drag – When you move the mouse while holding down the mouse button (usually the left) to select a range of cells. 9. File – A document that is stored on your computer. In Excel, a file is also known as a workbook. 10. Fill Down – A feature that allows you to copy information in an active cell to another cell or range of cells selected vertically. 11. Fill Right – A feature that allows you to copy information in an active cell to another cell or range of cells you have selected horizontally. 12. Formula- A combination of numbers and symbols used to express a calculation. 13. Formula Bar – A command line above the worksheet where text, numbers, and formulas are entered into a worksheet. 14. Function – A drop-down menu item and a button on the standard toolbar that allows you to select a formula that you wish to apply to data in your worksheet.  15. Headings – The identifying letters and numbers for columns and rows. Columns are identified with letters, rows with numbers. 16. Label – The identifying name that reflects the information contained in a column or row in a worksheet, such as name or date. 17. Row – In a worksheet, the horizontal spaces with the headings 1, 2, 3, and so on. 18. Sheet Tabs – Tabs you see at the bottom of your workbook file, labeled Sheet 1, Sheet 2, and so on. You can rename the tabs. They represent worksheets within the workbook. 19. Workbook – An Excel file that contains individual worksheets. Also called a spreadsheet file. 20. Worksheet – A “page” within an Excel workbook that contains columns, rows, and cells. PART 3 – Operators, Formulas and Functions What are Operators? Operators specify the type of calculation that you want to perform on elements in a formula—such as addition, subtraction, multiplication, or division. In this article, you'll learn the default order in which operators act upon the elements in a calculation. You'll also learn that how to change this order by using parentheses. Types of operators There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference. Operator Meaning Example Arithmetic + (plus sign) Addition 3+3 – (minus Subtraction 3–1 sign) Negation –1 * (asterisk) Multiplication 3*3 / (forward Division 3/3 slash) % (percent Percent 20% sign) ^ (caret) Exponentiation 3^2 Comparison = (equal Equal to A1=B1 sign) > (greater Greater than A1>B1 than sign) < (less than Less than A1= (greater Greater than A1>=B1 than or or equal to  equal to sign)