Module 3 - Word and Excel PDF

WORD AND EXCEL COMMANDS, FEATURE AND MORE PREPARED BY: ENGR. JHON LLOYD CABAHUG WORD COMMANDS, FEATURE AND MORE  Microsoft Word ► Microsoft Word is application software for writing texts, and different types of documents can be easily developed in a practical way ► In other words,...

WORD AND EXCEL COMMANDS, FEATURE AND MORE PREPARED BY: ENGR. JHON LLOYD CABAHUG WORD COMMANDS, FEATURE AND MORE  Microsoft Word ► Microsoft Word is application software for writing texts, and different types of documents can be easily developed in a practical way ► In other words, it is a word processor that allows you to create and edit text. As long as you know what the parts of Microsoft Word are and what they are used for. With this software, the user will be able to write and design texts where they will be able to use different fonts, colors, sizes, with the different commands of Word. Word Interface  Word Interface ► Quick Access Toolbar lets you access common commands no matter which tab is selected. By default, it includes the Save, Undo, and Repeat commands. You can add other commands depending on your preference ► The Ribbon Excel 2013 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains organized commands in three layers. Tabs or Menu: These are the horizontal menu appear at the top of the Ribbon and contain groups of related commands, i.e., File, Home, Insert, Design, Layout, References, Mailings, Review, View, and Help are examples of ribbon tabs. Groups: These are the organized related commands displaying below each tab or menu. The name of each group appears below the group on the Ribbon. For example, group of commands related to paragraph or group of commands related to the font, etc. Commands: Commands appear within each group as mentioned above. For example, when we press the “File” tab, some commands or functions such as “New”, “Open” “Save”, “Save as” etc. are displayed.  Word Interface ► Microsoft Word has two rulers – one is the horizontal ruler and the second is a vertical ruler. The horizontal ruler seems just under the Ribbon. It is used to set the size of the margins and tab stops position of the document. The vertical ruler appears on the left side of the document window and is used to measure the vertical position of elements on the page. ► The zoom slider allows you to resize the document to view it according to the user’s requirements. It allows you to zoom in or zoom out from 0% to 500% wide range. The zoom controls consist of a slider that you can slide left (zoom in) or right (zoom out); you can also click the plus or minus sign buttons to increase or minus decrease the zoom factor.  Word Interface ► Print Layout view − This view displays pages exactly as they will show in print. ► Read Mode − This displays a full-screen view of the document. ► Web Layout − This displays a document view that appears on a Web browser, such as Internet Explorer. ► Outline view− This lets you display your document in outline form. For this view, we will first create using Word’s standard heading styles. ► Draft view− This displays only the text of your document as it appears on the printed page. No headers and footers are shown in this view.  Word Interface ► The document or work area is the area where we can type of document, letter, memo, or simply a communication will be developed. ► Status Bar shows the total number of pages, word count, language, translator, error notifications, and sections among other information. ► Dialog Box Launcher a small arrow in the lower-right corner of many groups on the Ribbon that display some commands. By clicking this arrow, the downward window will open that provides more options about the group. Common Shortcut Keys Common Shortcut Keys  Format Font ► Most options to change the format of the text can be found on the Home tab, in the Font group.  Format Font 9. Underline ‐ Changes selected text to have a single line under the text. The 1. List of Fonts ‐ Changes the shape of the selected text. dropdown allows you to pick different underlines and underline colors. Use Ctrl‐U for a basic underline. 2. List of Font Sizes ‐ Changes the size of the selected text. You can type in your own custom size, the range is 1pt to 1638pt, including half sizes (e.g. 14.5pt) Use Ctrl‐[ or Ctrl‐] to increase and 10. Strikethrough ‐ Changes the selected to have a single line through the middle of decrease by one font point. the letters. 3. Grow Font ‐ Increases the size of the selected text, based on the list of font sizes. Use Ctrl‐> to 11. Subscript ‐ Changes the selected text to be small and lowered. Example: H2O increase the font based on the list of sizes. (“Subway goes down”). Use Ctrl‐= to subscript. 4. Shrink Font ‐ Decreases the size of the selected text, based on the list of font sizes. Use Ctrl‐< to 12. Superscript ‐ Changes the selected text to be small and raised. Example: x2 decrease the font based on the list of sizes. (“Superman goes up”). Use Shift-Ctrl‐= to superscript. 5. Change Case ‐ Changes the capitalization of your selected text. Choices include: Sentence 13. Text Effects ‐ Changes the text to have more dramatic appearance. This option is case.; lowercase; UPPERCASE; Capitalize Each Word; and tOGGLE cASE. only available in Office 2010. 6. Clear Formatting ‐ Change the selected text to the default settings. 14. Text Highlight Color ‐ Changes the mouse to a highlighter. Turn on this tool and drag over text to highlight/un‐highlight. 7. Bold ‐ Changes selected text appear darker, like writing with a marker instead of a pen. Use Ctrl‐B to bold. 15. Font Color ‐ Changes the selected text to the chosen color. When you choose a color it becomes the default. To see other color options, click More Colors... to see 8. Italic ‐ Changes selected text to appear slanted, leaning the letters to the right. Use Ctrl‐I to all the colors available. Italicize. 16. More Font – Opens the Font dialog box with more Font settings Font Dialog Box  Formatting Paragraphs 1. Bullets – Start a bulleted list or click the drop down arrow to choose a bullet style 13. Shading – Change the background color behind the text 2. Numbering – Start a numbered list or click the drop down arrow to choose a number style 14. Borders – Change the borders around the text, click the arrow to choose different styles 3. Multilevel List – Start a multileveled list or click the drop down arrow to choose different styles 15. More Paragraph – Opens the Paragraph Window to find most of the paragraph 4. Decrease Indent – Decrease the indent level of the current/selected paragraph(s) formatting options 5. Increase Indent – Increase the indent level of the current/selected paragraph(s) 6. Sort – Alphabetize the selected text 7. Show/Hide – Show or hide the non‐printing characters such as paragraph breaks, spaces and tabs 8. Align Text Left – Align the current/selected paragraph(s) on the left side 9. Center – Align the current/selected paragraph(s) in the center 10. Align Text Right – Align the current/selected paragraph (s)on the right side 11. Justify – Align the current/selected paragraph(s) on both sides 12. Line Spacing – Change the spacing between the lines of the current/selected paragraph(s) Alignment ⮚ The horizontal alignment can be changed by using these buttons: , in the Paragraph group of the Home Tab. Indentation ⮚ The left side of the paragraph can be indented by using these buttons:. They will increase or decrease the left indentation of the paragraph by half an inch. You can also move the left indent with the keyboard by selecting the paragraph and pressing Tab to increase and Shift‐Tab to decrease the indentation. Spacing ⮚ Line Spacing The space between each line in a paragraph can be changed using the Line Spacing button on the Home tab in the Paragraph group. This list shows the most common line spacing values. The Line Spacing Options... will open the format Paragraph window.  1. Put your cursor where you want to add the table of contents. 2. Go to References > Table of Contents. and choose an automatic style. How to Create Table of 3. If you make changes to your document that affect the table of contents, update the table of contents by right-clicking the table of contents and Contents choosing Update Field.  Create Citations: 1. Put your cursor at the end of the text you want to cite. 2. Go to References > Style, and choose a citation style. How to 3. Select Insert Citation Create Citations & Bibliography 4. Choose Add New Source and fill out the information about your source. Once you've added a source to your list, you can cite it again: 1. Put your cursor at the end of the text you want to cite. 2. Go to References > Insert Citation, and choose the source you are citing. 3. To add details, like page numbers if you're citing a book, select Citation Options, and then Edit Citation. Before you create a table of figures, you must add captions to all the figures and tables that you want included in your table of figures. Insert a Table of Figures 1. Click in your document where you want to insert the table of figures. 2. Click References > Insert Table of Figures. 3. You can adjust your Format and Options in the Table of Figures dialog box. Click How to OK Create Table Update a Table of Figures of Figures If you add, delete, change, or move captions, use Update Table so the table of figures reflects your changes. 1. Click on the table of figures in your document. This will highlight the entire table. 2. Click References > Update Table. 3. Select an Update in the in the Update Table of Figures dialog box. 1. Select Update page numbers if you need to adjust the page numbers. 2. Select Update entire table if you have moved figures or altered captions. Activity EXCEL COMMANDS, FEATURE AND MORE Excel Interface  Excel Interface ► Quick Access Toolbar lets you access common commands no matter which tab is selected. By default, it includes the Save, Undo, and Repeat commands. You can add other commands depending on your preference ► The Ribbon Excel 2013 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with several groups of commands. You will use these tabs to perform the most common tasks in Excel  Excel Interface ► In the formula bar, you can enter or edit data, a formula, or a function that will appear in a specific cell. In the image below, cell C1 is selected and 1984 is entered into the formula bar. Note how the data appears in both the formula bar and in cell C1 ► The Name box displays the location, or "name" of a selected cell. In the image below, cell B4 is selected. Note that cell B4 is where column B and row 4 intersect. ► The Backstage View (The File Menu) Click the File tab on the Ribbon. Backstage view will appear. Excel Interface Excel Interface ► The Worksheet Views Excel 2013 has a variety of viewing options that change how your workbook is displayed. You can choose to view any workbook in Normal view, Page Layout view, or Page Break view. These views can be useful for various tasks, especially if you're planning to print the spreadsheet. To change worksheet views, locate and select the desired worksheet view command in the bottom-right corner of the Excel window. Excel Interface ► To use the Zoom control, click and drag the slider. The number to the right of the slider reflects the zoom percentage.  Excel Interface ► The Worksheet Excel files are called workbooks. Each workbook holds one or more worksheets (also known as "spreadsheets"). Whenever you create a new Excel workbook, it will contain one worksheet named Sheet1. A worksheet is a grid of columns and rows where columns are designated by letters running across the top of the worksheet and rows are designated by numbers running down the left side of the worksheet.  Cell Basics ► Whenever you work with Excel, you'll enter information, or content, into cells. Cells are the basic building blocks of a worksheet. You'll need to learn the basics of cells and cell content to calculate, analyze, and organize data in Excel Understanding Cells ► Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2, 3). Each cell has its own name, or cell address, based on its column and row. In this example, the selected cell intersects column C and row 5, so the cell address is C5. The cell address will also appear in the Name box. Note that a cell's column and row headings are highlighted when the cell is selected. You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would bewritten as A1:A5 Cell Content Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain several different kinds of content, including text, formatting, formulas, and functions. ► Text Cells can contain text, such as letters, numbers, and dates. ► Formatting Attributes Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%. You can even change a cell's background color. ► Formulas and Functions Cells can contain formulas and functions that calculate cell values. In our example, SUM(B4:B7) adds the value of each cell in cell range B4:B7 and displays the total in cell B8  ► You can also access additional paste options, which are especially convenient when working with cells that Paste Options contain formulas or formatting. ► Or Rather than cutting, copying, and pasting, you can drag and drop cells to move their contents. 1. Select the cell(s) you wish to move. 2. Hover the mouse over the border of the selected cell(s) until the cursor changes from a white cross to a black cross with four arrows. 3. Click, hold, and drag the cells to the desired location. 4. Release the mouse, and the cells will be dropped in the selected location. Fill handle ► There may be times when you need to copy the content of one cell to several other cells in your worksheet. You could copy and paste the content into each cell, but this method would be very time consuming. Instead, you can use the fill handle to quickly copy and paste content to adjacent cells in the same row or column. Fill Handle ► The fill handle can also be used to continue a series. Whenever the content of a row or column follows a sequential order, like numbers (1, 2, 3) or days (Monday, Tuesday, Wednesday), the fill handle can guess what should come next in the series. In many cases, you may need to select multiple cells before using the fill handle to help Excel determine the series order. In our example below, the fill handle is used to extend a series of dates in a column. Find and Replace ► When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.  Formatting Cells ► All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. You can also apply number formatting to tell Excel exactly what type of data you’re using in the workbook, such as percentages (%), currency ($), and so on. Font Formatting ► By default, the font of each new workbook is set to Calibri. However, Excel provides a variety of other fonts you can use to customize your cell text. In the example below, we'll format our title cell to help distinguish it from the rest of the worksheet. Text Alignment ► By default, any text entered into your worksheet will be aligned to the bottom-left of a cell. Any numbers will be aligned to the bottom-right of a cell. Changing the alignment of your cell content allows you to choose how the content is displayed in any cell, which can make your cell content easier to read. Cell Borders and Fill Colors ► Cell borders and fill colors allow you to create clear and defined boundaries for different sections of your worksheet. Formatting text and numbers ► One of the most powerful tools in Excel is the ability to apply specific formatting for text and numbers. Instead of displaying all cell content in exactly the same way, you can use formatting to change the appearance of dates, times, decimals, percentages (%), currency ($), and much more. Modifying Columns, Rows and Cells ► By default, every row and column of a new workbook is always set to the same height and width. Excel allows you to modify column width and row height in different ways, including wrapping text and merging cells. Inserting, deleting, moving, and hiding rows and columns ► After you've been working with a workbook for a while, you may find that you want to insert new columns or rows, delete certain rows or columns, move them to a different location in the worksheet, or even hide them. Wrapping text and merging cells ► Whenever you have too much cell content to be displayed in a single cell, you may decide to wrap the text or merge the cell rather than resizing a column. Wrapping the text will automatically modify a cell's row height, allowing cell contents to be displayed on multiple lines. Merging allows you to combine a cell with adjacent, empty cells to create one large cell.  Formulas and Functions ► One of the most powerful features in Excel is the ability to calculate numerical information using formulas ► Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we'll show you how to use cell references to create simple formulas. Mathematical Operators ► Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents. ► All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates. Understanding Cell References ► While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula. ► By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below: Complex Formulas ► A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations. ► Excel calculates formulas based on the following order of operations: 1. Operations enclosed in parentheses 2. Exponential calculations (3^2, for example) 3. Multiplication and division, whichever comes first 4. Addition and subtraction, whichever comes first Relative cell references ► By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns. Absolute cell references ► There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant. ► An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both. ► You will generally use the $A$2 format when creating formulas that contain absolute references. The other two formats are used much less frequently.  Functions ► A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references. Formula =A1+A2+A3+A4+A5+A6+A7+A8 Function =SUM(A1:A8) Functions ► In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is an equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. Functions ► Excel has a variety of functions available. Here are some of the most common functions you'll use: ∙ SUM: This function adds all of the values of the cells in the argument. ∙ AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument. ∙ COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range. ∙ MAX: This function determines the highest cell value included in the argument. ∙ MIN: This function determines the lowest cell value included in the argument. VLOOKUP ► Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) ► Basically, VLOOKUP lets you search for specific information in your spreadsheet. For example, if you have a list of products with prices, you could search for the price of a specific item. We’re going to use VLOOKUP to find the price of the Photo frame. You can probably already see that the price is $9.99, but that’s because this is a simple example. Once you learn how to use VLOOKUP, you’ll be able to use it with larger, more complex spreadsheets, and that’s when it will become truly useful. ► As with any formula, you’ll start with an equal sign (=). Then, type the formula name. =VLOOKUP(“Photo frame” The second argument is the cell range that contains the data. In this example, our data is in A2:B16. As with any function, you’ll need to use a comma to separate each argument: ► =VLOOKUP(“Photo frame”, A2:B16 Note: It’s important to know that VLOOKUP will always search the first column in this range. In this example, it will search column A for “Photo frame”. In some cases, you may need to move the columns around so that the first column contains the correct data. The third argument is the column index number. It’s simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the price of the item, and the prices are contained in the second column. That means our third argument will be 2: ► =VLOOKUP(“Photo frame”, A2:B16, 2 The fourth argument tells VLOOKUP whether to look for approximate matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Since we’re only looking for exact matches, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses: ► =VLOOKUP(“Photo frame”, A2:B16, 2, FALSE) And that’s it! When you press enter, it should give you the answer, which is 9.99 INDEX MATCH ► Formula: =INDEX(range, MATCH(lookup_value, lookup_range, match_type)) ► perform the same operations as VLOOKUP. INDEX returns the value of a cell in a table based on the column and row number. MATCH returns the position of a cell in a row or column. Combining these two functions we can look up a value both horizontally and vertically. The main advantages of using INDEX MATCH than VLOOKUP are: 4. Higher processing speed - The difference in 1. Dynamic Column Reference - VLOOKUP requires a static column performances between VLOOKUP and INDEX/MATCH will reference whereas INDEX MATCH requires a dynamic column be negligible if your table_array is small. But if your reference worksheets contain a lot of rows and formulas, INDEX 2. Insert/Delete Columns Safely - VLOOKUP uses a static column MATCH will work much faster than VLOOKUP because reference. This breaks the formula each time you add/delete a new column. You can manually set the formula to refer the correct Excel will have to process only the lookup and return column. But it is a lot of work especially when you have a large data columns rather than the entire table array. set. INDEX MATCH solves this problem by using a dynamic column reference. You can add/delete columns without distorting the array. 5. Lookup Value Position - VLOOKUP will only 3. Lookup Value Size Limit - You need to make sure the total length of work if the lookup value is in the first column. VLOOKUP your lookup criteria should not exceed 255 characters, otherwise you cannot look to its left. However, INDEX MATCH solves this will end up having the #VALUE! error. But INDEX MATCH can lookup problem as it performs the lookup both horizontally and values more than 255 characters in length. vertically. So, it doesn’t require the lookup value to be in the first column, it can be anywhere.  IF Statements ► Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false). ► Example Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota” =IF(C3>D3, “Met Quota”, “Did Not Meet Quota”) This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.  Freezing Panes ► Whenever you're working with a lot of data, it can be difficult to compare information in your workbook. ► Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, such as the ability to freeze panes and split your worksheet ► You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you'll be able to scroll through your content while continuing to view the frozen cells. Sorting Data ► As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways. ► When sorting data, it's important to first decide if you would like the sort to apply to the entire worksheet or just a cell range. ∙ Sort sheet organizes all of the data in your worksheet by one column. ∙ Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet. Filtering Data ► If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need  Working with Charts ► Creating a chart in Microsoft Office Excel is quick and easy. Excel provides a variety of chart types that you can choose from when you create a chart. Excel offers Pie, Line, Bar, and Column charts to name but a few. Showing data in a chart can make it clearer, more interesting and easier to read. Charts can also help you evaluate your data and make comparisons between different values. Working with Charts ► Excel has several different types of charts, allowing you to choose the one that best fits your data. In order to use charts effectively, you'll need to understand how different charts are used. Types of Charts: ❑ Column charts use vertical bars to represent data. They can work with many different types of data, but they're most frequently used for comparing information. ❑ Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time. ❑ Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's easy to see which values make up the percentage of a whole. ❑ Bar charts work just like Column charts, but they use horizontal bars instead of vertical bars. ❑ Area charts are similar to line charts, except that the areas under the lines are filled in. ❑ Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time. Printing Workbooks ► There may be times when you want to print a workbook to view and share your data offline. Once you've chosen your page layout settings, it's easy to preview and print a workbook from Excel using the Print pane.