Microsoft excel 2016 notes free -

Looking for:

Free Excel Tutorial at GCFGlobal. 

Click here to DOWNLOAD

     

Microsoft excel 2016 notes free. Microsoft ® Office Excel 2016: ® Part 1

 

Figure The Quick Access Toolbar. The Mini toolbar is displayed when you right-click the active cell on a worksheet. It is a small, rectangular pop-up window that contains a set of common text editing and formatting commands that you can use to work with the data in the active cell. In Excel , there are actually multiple versions of the Mini toolbar: the main version that is displayed when you right-click an active cell, and variations of the main Mini toolbar that are displayed when you right-click various other objects on worksheets, such as images and charts.

Context menus also appear when you select or right-click particular objects or data within worksheets. Context menus display a list of commands and options that pertain to working with the specific type of content you have selected. Figure Variations on the Mini toolbar and context menus in Excel Scenario You are now familiar with the overall general layout of the Excel environment.

You'd like to focus on discovering where some of the commonly used commands are. You decide to use ScreenTips to help you begin to identify some of the commands you've been wondering about. The Tell Me feature will also help you find some of the commands you haven't yet located on the ribbon.

You also want to look over one or two of the dialog boxes to see what commands are available there. Additionally, you've just received an email message from the HR department informing you of an error on the holiday schedule, which you'll need to correct. The holiday schedule workbook is already open, which works out well as you also want to add a few entries to the worksheet to help you plan your vacation time.

Use ScreenTips to identify common commands. Examine the commands in a dialog box. Find another dialog box with Tell Me. Add data to a cell. Replace existing data in a cell. Save and close the file. Saving the file by using this method will overwrite the existing data file.

Inform the students that a copy of the original data file has been included in the solutions folder for this lesson if students wish to practice this activity again. It's likely you will be called upon to work with data for a number of different purposes and regarding a number of different subjects.

You may also need to present similar information to multiple audiences. As such, you'll find yourself creating a variety of different workbooks that you will need to save as separate items, as well as saving multiple versions of the same workbooks.

Microsoft Excel makes it easy to accomplish these tasks. You can create a new blank workbook to start from scratch, or you can decide to start with an existing Excel workbook template, and then make changes to suit your needs. Let students know templates will be covered in depth in Lesson 4.

Figure The New tab in the Backstage view. The following table describes the various elements of the New screen in the Excel Backstage view. Start searching button Execute a search based on the search term s entered in the Search for online templates field. Suggested searches section Search for Office. These categorized searches are also broken down into sub-categories for more precise searching. Blank workbook tile Create a new, blank workbook.

This is an XML-based file format that allows Excel to compress files when you close them, making them up to 75 percent smaller than files saved in the previous Excel file format, XLS. The XLSX file format also provides other benefits over the previous file format, such as easier recovery of damaged files, increased security and protection functionality, and greater compatibility with other applications. Microsoft Excel also supports a host of other file types that enable you to open workbook files from previous versions of Excel and share files in a variety of formats with other users.

The following table provides a partial list of the file types available in Excel Excel Binary Workbook. This file type may not be compatible with some applications that work only with XML-based files.

Excel Workbook. The XLS format is a binary file format, which isn't as compatible with other computer applications as the newer, XML-based file format. Excel Template. This format is used to save workbooks as templates so that you can create new workbooks based on the template contents, layout, and format.

You use the Save command to save changes to an existing workbook without changing the file name or the file location. You use the Save As command to save new workbook files or to make changes to existing files, such as the file name and location. Both the Save and Save As commands are accessible in the left pane in the Backstage view.

The Quick Access Toolbar also provides access to the Save command by default. Note: If you select Save to save a new workbook file, Excel automatically displays the Save As screen, as you must specify a location and a file type when saving new files. The Save As Screen The Save As screen contains the commands and options you will use to select a location to save The Save As Screen your workbook files, either on your computer, on an attached storage device, or in a remote location such as a network share or an online file storage service.

The following table describes the four main options on the Save As screen. OneDrive is a file storage and sharing service the lesson on workbook that enables you to store files online or "in the cloud" for easy access sharing and from any location that has Internet access. You must have a Microsoft collaboration. This PC This option acts as a shortcut to display a list of locations that you've recently saved your workbooks to. The shortcuts are divided into various sections based on time, such as Today and Yesterday.

This PC also shows where your currently opened workbook is saved, under the Current Folder section. Selecting one of the shortcuts opens a Save As dialog box to that location. In this dialog box, you can name your file and select the appropriate file type. Browse This option immediately opens the Save As dialog box to your local Documents folder, or to the current workbook's location if it's already been saved.

You can use this option to quickly browse to a specific location where you want to save your workbook. Compatibility Mode Compatibility Mode When you open a workbook file in Excel that was created by using Excel or an earlier version , Excel opens the file in Compatibility mode and the Title bar displays the file name with the text [Compatibility Mode] next to it.

You can open and work with files in Compatibility mode to preserve the original file format, allowing you to subsequently open the file in previous versions of Excel. Keep in mind that some Excel features are not available in all versions of Excel. Figure The Title bar of a workbook file open in Compatibility mode. The Convert Option The Convert Option When you open a workbook in Excel that was created in a version of Excel, and you no longer need to keep the workbook in the previous file format, you can convert the workbook into the current XLSX format.

Using the Convert option provides you with access to all of the features and functionality available in Excel Often, converting a file to the newer file format will also reduce the size of your workbook file. Figure The Convert option.

The previous version of the file is no longer available for you to work with. You can access the Convert option from the Info tab in the Backstage view of any workbook that is open in Compatibility mode. The Compatibility Checker The Compatibility Checker allows you to test the compatibility of objects and data in an Excel The Compatibility workbook when you intend to save it in an earlier Excel file format.

This is typical when you need to Checker share a file you created in a newer version of Excel with a user who has an older version of Excel installed on their machine. Selecting the Check Compatibility command opens the Microsoft Excel - Compatibility Checker dialog box, in which you can view a list of features in your Excel file that are not supported in earlier versions of Excel.

Excel will also run the Compatibility Checker automatically when you attempt to save a current Excel workbook file in the previous file format. Unchecking the Check compatibility when saving this workbook check box allows you to save in previous versions without the Compatibility Checker running automatically. However, be careful when you uncheck this feature; you will lose certain functionality when you save back to previous versions.

There are two levels of compatibility issues the Compatibility Checker can detect: minor loss of fidelity and significant loss of functionality. With minor compatibility issues, you can likely save the workbook in an earlier file format with limited loss of functionality.

Common changes when saving back to an earlier file format include the removal of table and text formatting not supported in earlier versions. Doing so may cause you to lose data, experience formula or calculation failures, or experience other serious issues. Scenario Now that you're more familiar with the Excel UI and some of its basic commands, you feel comfortable enough to create your first new workbook.

A colleague has emailed you some basic sales data for two new products, and you expect more data soon. You decide to create a new workbook, enter the data, and then save the workbook so you can add more data to it as it comes in. Another colleague has asked for a copy of the file, but she works in Excel So you'll also have to save a copy of the file in an earlier format.

Create a new blank workbook. Add column labels for the data. Remind students that 3. In the Product column, enter the product names. In the Quantity column, enter the quantity data. In the Price column, enter the price data. Save the workbook in the default Excel file format. Save a copy of the workbook file in a previous file format.

You'll need to be familiar with what these data types are and how Excel deals with them. Having a working knowledge of how Excel "sees" data is an important first step to developing the skills you'll need to crunch your numbers and keep track of your important information. In this topic, you'll use fundamental Excel features to help you enter data.

Data Types One of the most fundamentally important things to understand about Excel is that it is not a "what Data Types you see is what you get" type of environment. Often, the value or text that appears within a cell is not what is actually stored in the cell.

A simple example of this is the result of a calculation. If you have a formula entered in a cell, by default, the cell will display the result of the formula. But 2 is not the actual cell data; the formula is. Although this may, at first, not seem highly important, as you begin to work with the more complex functionality in Excel, it will become a critical concept to understand.

Note: When you're in Edit mode, the cell displays its actual content, similar to the way the Formula Bar displays cell content. So if a cell contains a formula, while you edit the content, the cell displays the formula. When you're in Enter mode, Excel displays the formula result in the cell. Figure The difference between what you see in a cell and what's actually in the cell.

Although Excel cells can contain an incredible array of content, there are really only a few general categories of data that you'll work with on a regular basis.

Data Category Description Values Numeric constants that do not change unless you edit the cell contents. Examples include 1, , These can largely be viewed as labels for related sets of data on a worksheet. Examples include "Sales," "Q1," and "Total. Formulas are dynamic, so the displayed value can change if you change the cell data "feeding" the formula. Dates and times Date and time values. These can be used both as simple labels or as part of certain mathematical or logical operations.

The Cut, Copy, and Paste commands are a good example of this. You will use the Cut, Copy, and Paste commands either to make a copy of cell data and place it in another cell, or to remove data from one cell and put it in another. Copied data is temporarily stored on a Microsoft Office tool known as the clipboard. Data on the clipboard can be reused in other locations on the same worksheet, within the same workbook, and in other workbooks and applications.

To refresh your memory, or in case you are not familiar with the Cut, Copy, and Paste commands, the following table offers a description of each. Students may notice the Paste command also Command Performs This Action displays a drop-down arrow that provides Cut Removes data from the selected cell or removes the selected data, and places users with access to a a copy of it on the clipboard.

Those will be Copy Makes a copy of the data in the selected cell or a copy of the selected data, covered in greater detail and places the copy on the clipboard. Paste Places the data that was most recently added to the clipboard in the destination cell or location.

One important distinction to make in Excel regarding the use of these commands is the difference between using Cut, Copy, and Paste on entire cells versus using them on selected data. If you select a cell, using the Cut or the Copy command will affect all of the data within the cell; remember that what you see isn't necessarily the data in the cell. You can also select a portion of the cell data to cut or copy. You can do this within the cell if it's in Edit mode, or in the Formula Bar with the desired cell selected.

The same is true of the Paste command. You can either paste the clipboard content into an entire cell or you can place it alongside other cell content in a cell in either Edit mode or in the Formula Bar. Consider pointing out When you use the Cut or the Copy command to copy content to the clipboard, Excel displays an the keyboard shortcuts animated, dashed-line box, often referred to as "marching ants" or a marquee selection, around your for Cut, Copy, and selection to help you verify that you have copied the correct content.

Fortunately, Excel, like many other The Undo and Redo Office applications, provides you with the Undo and Redo commands to help you correct errors as Commands you work. The Undo command will cancel out the last action you performed, or the last several actions you performed, so you can correct any mistakes you've made while working with your workbooks.

Once you've used the Undo command, Excel activates the Redo command, which will cancel out the last undo action or a series of undo actions. The AutoFill Feature Excel includes a number of features meant to make your life a bit easier. The AutoFill feature attempts to recognize an existing pattern in the data you have already entered, and then apply that pattern to filling in additional cells.

Let's look at an example. Say you enter the following values into the following cells in column A: A1: 1, A2: 2, A3: 3, and A4: Consider demonstrating 4.

Clearly, you are attempting to create a sequential numbered list in the first column, or at least this functionality to the that's what Excel will assume. If you use the AutoFill feature to fill in the remaining cells in column class. Encourage A, Excel will follow the pattern and fill in the remaining cells with 5, 6, 7, 8, 9, 10, and so on.

If you students to experiment would like AutoFill to recognize a repeating pattern, say 1, 2, 3, 1, 2, 3, you would need to enter at with a variety of different data sets. If you have least two cycles of the pattern to be sure Excel recognizes it as a pattern and not a sequence of students participating numbers. If you start with only a single value, Excel will simply repeat it. AutoFill works for text as remotely, ask them to well as numeric values, so Excel would, for example, recognize a pattern such as lettering the first screen share interesting column instead of numbering, entering the days of the week, or entering the months of the year.

To use the AutoFill feature, you must first select the sequence of cells you want the pattern to be based on. When you select a cell or a range, Excel displays the cell or range within a solid green border. At the bottom-right corner of the border, Excel displays a fill handle. To use the AutoFill feature, you simply drag the fill handle until the border surrounds the desired range of cells, and then release it.

When the mouse pointer is directly over the fill handle, it is displayed as a thin black plus symbol. The AutoFill feature can also assist you with entering duplicate text entries in the same column. If you begin to type something into a cell that matches the beginning of another cell's content, Excel will attempt to automatically complete the entry for you to match the existing content. If you intended to enter a duplicate entry, you can simply press the Enter key, and Excel will automatically complete the entry.

However, as you type, if the sequence of characters deviates from the other cell's content, Excel will stop displaying the entry and will simply let you continue to type the desired cell content. This works only with entries that consist of just text or a combination of text and numbers. Excel will not automatically complete duplicate entries of numeric values. After you release the fill handle, Excel displays a small icon on the lower-right side of the range.

Selecting that icon opens a menu, providing you with access to a series of options for choosing how to apply the AutoFill. These options can change depending on the type of data you have entered into the range. The following table describes the most commonly used AutoFill options. AutoFill Option Description Copy Cells Ignores any recognizable pattern and fills the remaining cells with the same data the originally selected cells contain. Fill Series The default AutoFill option.

Excel applies the pattern it recognizes in filling the remaining cells. Fill Formatting Only Applies any formatting in the originally selected cells to the remaining cells without populating the cells with content. Fill Without Formatting Fills the remaining cells with data based on the recognized pattern while ignoring any formatting applied to the originally selected cells.

Flash Fill If Excel recognizes a pattern in cell entries in one column based on the cells in other columns, it can fill in the remaining cell entries following that pattern. Flash Fill In addition to being an AutoFill option, the Flash Fill feature can automatically recognize patterns Flash Fill across rows as you enter data, and then copy those patterns down a column of entries.

The key difference between this and the AutoFill feature is that it depends on a recognizable pattern based on the entries in cells in other columns.

Essentially, you can use this feature to combine entries, or parts of entries, from cells in the same row to another cell in that row, and then copy that combined data down a column. Let's look at a simple example to illustrate the point. Suppose you have a worksheet that lists employee names. The last names are in column A and the first names are in column B.

But you also need a column for each employee's email address. These email addresses always follow the same pattern: firstname. So, in the first row in column C, you manually type the first employee's first name followed by a period, then the employee's last name, then develetech. Excel can recognize that this data is based on the entries in the first two columns, and then it can copy all of the names down column C, following the pattern, for each of the employees.

You have to begin entering subsequent entries by following the same pattern before it can do so. Flash Fill can end up saving you an incredible amount of time. It works only down columns. Note: The Flash Fill feature will work only if there are no empty columns between the original data and the column you're trying to fill following a pattern.

There can, however, be columns of data that don't relate to the pattern in between the cells containing the original data and the cells you are trying to fill. Figure The Flash Fill feature recognizes that cell C3 is following the pattern of C2 using data from its adjacent cells. It can then fill in the pattern for the rest of the cells in column C.

The Clear Command Because you need to enter data into worksheet cells, it stands to reason that you will also need to delete cell data from time to time. The most basic method of doing this is to select the cell that contains the data you wish to remove and then press the Delete key.

Excel also provides an alternative with the Clear command, which gives you access to a number of options for removing cell content that don't always include removing everything from the cell. The Clear command is available in the Editing group on the Home tab. The following table describes each of the Clear command options.

Clear Formats Only formatting applied to the selected cell s. The content is left in place, including any existing comments. Clear Contents Only the contents of the selected cell s , but not the formatting. Clear Comments Only comments from the selected cell s. Clear Hyperlinks Hyperlinks from cell contents. The formatting is left in place, including formatting applied to the text when the hyperlink was created. Scenario Your colleague has emailed you a text file with some additional new products that Develetech will be adding to its catalog.

In order to keep track of the new product lines all in one place, you decide to add the new items to the My New Products. In addition to tracking the cost and quantity of your new products, you also want to list each product's unique number and product code as identifiers.

These will be used both internally and on the public-facing online store to assist with inventory. These identifiers follow certain patterns, so rather than wasting time entering them manually for each product, you'll let Excel do the work for you. Open the My New Products. Ensure that students are b From the Open screen, ensure that Recent is selected and, in the Today list, select My New opening the version of Products. Open the Future Products. While c Double-click the Future Products.

Copy and paste the new product names into the My New Products. If students ask about the Paste Options menu that appears after pasting the content, let them know that it will be covered later in the course. It will have no effect on the current activity. Use the AutoFill feature to add unique product numbers for each new product entry.

Note: You can also drag the fill handle manually, but double-clicking is a faster way of populating every cell down a column that has adjacent row data. Use Flash Fill to quickly generate a product code based on the product name and number. Code and then press Enter. If students notice that the Prod. Code column label spills over onto the next cell, assure them that they will learn how to modify the appearance of cells in a later lesson. Save and close the My New Products.

Excel comes packaged with its own Help system that can assist you in finding answers to your questions. Taking the time to learn how to use Excel Help now could save you countless hours of research down the road. Microsoft Excel Help Microsoft Excel Help provides you with access to articles that can answer your Excel questions and show you how to perform various tasks within the application. Excel Help checks Microsoft Office's support website for these articles, which can include text, screenshots, and video.

In order to access Help articles, you must have an active Internet connection. You can also reach Help on specific topics by searching for the topic in Tell Me. You can use The Excel Help the Excel Help window to search for, browse through, and review the various web articles Window available to you. Figure The Excel Help window. The Excel Help window is divided into a number of sections and displays a variety of commands to help you locate the desired Help resources.

The following table describes each of these in some detail. Search field Enter search terms for locating Help resources. Search button Execute Help searches. Content section Display the results of a search or the contents of an online Help article. This activity requires an Scenario Internet connection. As you've been working with Excel more and more, you have come across some features that you have some questions about. You have heard there is a particular command that you might have use for, but you want to learn more about what it does first.

You decide to use the Excel Help system to find out more about the application. Time permitting, allow students to run their own 1. Open Excel Help. Search for Excel Help resources online.

It's not important that c Review the content. The d On the Excel Help toolbar, select the Home button. Close the Excel Help window. Excel Help to find information. You navigated the Excel user interface, used Excel commands, created and saved a basic workbook, entered cell data, and used the Excel Help system. As you build upon these foundational skills, you will begin to unlock Excel's robust functionality and discover the power that lies within your organizational data.

Encourage students to use the social Do you think you'll prefer to use the mouse or the keyboard navigation options as you develop your networking tools workbooks? Some students may be uncomfortable at first with the ribbon and Backstage view, continued learning.

They might find that it'll take some getting used to. Still, others may see the value in the ribbon system, especially from a visual point of view. Students may see the Tell Me feature as a good equalizer when they're unable to find what they're looking for with the ribbon. From the Course screen you can also access available resources for a more continuous learning experience.

Excel contains an incredible array of functionality to help you do this, particularly when it comes to working with numerical data. In order to take full advantage of Excel's abilities to quickly, efficiently, and accurately calculate data, you must first understand how Excel thinks, and how to tell it what to do. In this lesson, you'll do just that. Every time a single value changes, you might have to change entire rows or columns of data.

Keeping up with such calculations manually is not only impractical and tedious, but it is also unnecessary and, in some cases, nearly impossible. Why not have Excel do it for you? Performing calculations is one of the most critical, foundational tasks in Excel, forming the basis for nearly all of the data analysis you'll need to perform.

By gaining a solid, clear understanding of how Excel performs such calculations, you'll save yourself valuable time, avoid a ton of headaches, and ensure a level of accuracy not possible when performing the same calculations on your own. Excel Formulas Excel Formulas Excel formulas perform simple or complex mathematical computations in worksheets. You can use formulas to perform tasks such as adding up a row or a column of numbers, multiplying sales figures by commission rates, and applying tax to sales.

One of the key benefits of using formulas in Excel is that you can change some of the values used in the formulas and, by default, Excel will automatically adjust the calculations accordingly. It is important to remember that, in Excel, what you see isn't necessarily what you get. When you enter a formula into a worksheet cell, by default, Excel will display the result of calculating the formula in the cell, and not the formula itself.

Excel can perform calculations by using fixed numbers, or by referring to values in other cells. This is one of the truly powerful features of using Excel to perform calculations. Excel provides you with an incredible array of options for performing calculations in your workbooks and worksheets. Figure A formula in a worksheet cell. As you've seen, the Formula Bar allows you to edit cell contents and select, navigate to, and view the contents of selected cells.

It's also useful for quickly inserting pre-existing formulas. Additionally, to the left of the Formula Bar text box are buttons that provide supplementary options. The following table describes the Formula Bar options in greater detail.

Selecting the Cancel button will undo any changes you have made to a cell since selecting it and will keep that cell active. Basically, it reverts the cell back to its state before you began editing it.

When you select the Enter button, Excel enters whatever content is in the active cell and keeps the cell active.

Insert Function button The Insert Function button opens the Insert Function dialog box, providing you with access to a wide variety of pre-existing Excel formulas. Elements of Excel Formulas The first rule of using formulas in Excel is that all formulas begin with an equal sign. This may seem A Basic Mathematical a bit counter-intuitive at first, as you are used to seeing figures on both sides of a formula when it's Formula written out.

But Excel displays formula results in cells, which takes care of the result side of the equal sign. You enter the expression side of a formula after the equal sign.

To understand this better, let's first look at a simple mathematical formula. Formulas basically consist of an expression on one side of the equal sign and a result on the other. Expressions consist of a series of constants, variables, and mathematical operators.

Operators indicate where to perform such basic computations as adding, subtracting, multiplying, dividing, calculating exponents, and so on. Here are the basic elements of a mathematical formula as written on paper. Elements of Excel Excel formulas work pretty much the same way. When you enter a formula in a worksheet cell, the Formulas cell displays the result. The equal sign and the mathematical expression make up the content that's This is an excellent actually entered in the cell.

In Excel formulas, you can think of constants as numbers you manually opportunity to enter into formulas, and variables as references to other cells. When you manually type a number demonstrate how into an Excel formula, that number remains the same unless you manually edit it; this is referred to constants and as "hard coding" the value in the formula.

When you enter a cell reference in an Excel formula, the references work in Excel result of the calculation will change if you change the value in the referenced cell. Change the value in A2 to demonstrate the result changing automatically. Figure An Excel formula. Basic Excel formulas consist of the following elements: the equal sign, constants, references, and mathematical operators. The equal sign tells Excel to perform a calculation based on the formula components and then to display the result of the calculation in the cell.

All formulas in Excel must start with an equal sign. Constants Numbers and text that do not change unless manually altered. References Essentially, the variables in Excel formulas. When you include a reference to a cell or a range, Excel uses the value s from that cell or range to perform the calculation.

Mathematical operators Symbols that specify the kind of calculation that Excel should perform on the elements of a formula. These are simply symbols Excel uses to identify the calculations it should perform. This is how Excel determines The Order of Operations which operation to perform first, second, third, and so on in a complex formula.

It is important that Consider mentioning the you understand how Excel will compute a formula before you create one to ensure that Excel will mnemonic device perform the calculation exactly as you want it to. Note: While often used for subtraction, the minus sign — can also be used to denote a negative value. In these cases, the minus sign takes greater precedence than exponents, allowing you to calculate the exponential value of a negative number.

Figure Excel performs mathematical computations in a specific order. In fact, there are four different kinds of operators you can use to perform calculations. Reference operators are one of the most important. Reference operators tell Excel which cell or range values to use as variables in your formulas. There are three reference operators: the comma, the colon, and the space. Other than the reason provided, using Reference Symbol Function intersection operators is Operator a good practice in avoiding direct cell Comma , The comma is used as a union operator, which tells Excel to references.

This will include each reference in a series of references. This is similar become more apparent to how you would use a comma when writing a serial list; you when students learn are telling Excel to include each of the references. Colon : The colon is used as a range operator, which you've already seen when selecting a range of cells. The colon tells Excel to include all cells in a range between the cell references on either side of the colon.

This tells Excel to look for a value in the cell where two ranges intersect. For example, A9:J9 E2:E22 tells Excel to look for the value in the cell where these two ranges intersect, which in this case would be the value in cell E9.

If you have a spreadsheet of salespeople as the rows and their quarterly sales as the columns, you can use an intersection to find how well Salesperson X did in Q3. While you might be able to find this information by simply looking at the spreadsheet, this can become a hassle in larger spreadsheets that require scrolling through multiple pages. Note: Excel reads a blank space only as an intersection operator if no other operator is present between cell or range references.

If you enter another reference operator between the cell or range references, Excel will read the operator and ignore the spaces.

Here, the spaces don't matter because of the commas. Excel will not look for the intersection, which in this case makes sense as the cells don't actually intersect. Scenario Develetech plans to introduce five new products. Company management wants you to analyze the projected sales figures to determine how much the company can plan to profit from the new items.

You have entered the sales estimates into an Excel worksheet. Now, you must use Excel formulas to get Excel to perform the calculations for you. Open the New Product Income. Calculate the sales before tax based on the sales estimates for each product. Ask students to explain Note: Although Excel displays cell and range references, and many other what this formula will elements of formulas and functions, in all capital letters, it is not necessary to calculate.

Consider type them in all capital letters. Excel's functionality enables it to determine doing this for all what is a formula or function and what is standard text based on the context of formulas in the activity. Excel will automatically display formulas and functions in all capital letters, even if you type them as lowercase letters. Calculate sales with tax. Subtract expenses from sales before tax to calculate the estimated profit on the new product sales. Save the file as My New Product Income.

Excel provides you with the ability to do this quickly and easily through a massive set of built-in formulas.

Excel includes functionality to help you fill in and interpret these formulas, enabling you to focus more on the information you need to extract from your data and less on the mathematical operations themselves. Taking the time to learn how to use this functionality now will save you time, effort, and possibly a few headaches down the road. Functions In Excel, functions are simply built-in, predefined formulas that you can quickly and easily insert into Functions worksheet cells.

Like formulas, all functions begin with an equal sign. Unlike formulas, in functions The concepts in this the equal sign is followed by the function name and then a set of arguments in parentheses, which are topic are covered in a lot separated by commas.

Arguments can be cell references, constants, formulas, or even other of detail. Consider using functions or logical values. Functions use their arguments in specific ways to calculate a result. The the activities to cover function name is typically the name, or an abbreviated version, of the actual mathematical function. Remind would use the EXP function to calculate an exponential expression. Each function has its own students they can use specific structure and order of arguments.

You can manually type functions into worksheet cells or the book as a reference you can enter them by using various commands and dialog boxes. Figure The SUM function with a single argument. The Function Library Group Excel provides you with a central location for accessing all of its available functions: the The Function Library Function Library group. The Function Library group contains a set of menus that organize Excel Group functions according to specific categories for ease of reference.

You can access the Function Library group on the Formulas tab. The following table provides a brief description of the function categories in the Function Library group. When you first install possible function. Excel, you can access a set of commonly used functions from this Database functions must menu.

Logical Functions that determine if an argument is true or false, or if it meets other logical conditions. Text Functions that change text values, such as making text all capital letters or converting numbers into dollar amounts.

You might use these, for example, to determine how many work days occur between two specific dates. More Functions A set of menus that contain some higher-level and less commonly used functions, such as engineering and statistical functions. The Insert Function dialog box also displays a brief description of whatever function you have selected and provides a link to open the Excel Help window, which automatically displays help content on the currently selected function.

The following table describes the various elements of the Insert Function dialog box. For example, you could type "add numbers together" or "find the average of a set of numbers. Go button Execute a function search. Or select a category drop- Filter the available functions by category. If you perform a search, down menu this menu defaults to the Recommended setting and the search results will appear in the Select a function menu.

Even if you've entered a search query, if you change the setting here to any category other than Recommended, the Select a function menu displays all functions in the selected category, effectively ignoring the search query. Select a function menu View a list of available functions depending on your search query or your selection in the Or select a category drop-down menu.

Function description View a brief description of the currently selected function. Help on this function link Open the Excel Help window to display an article about the currently selected function. The Help on this function link performs the same task here as it does in the Insert Function dialog box. Figure The Function Arguments dialog box assists you with the process of entering function arguments. The following table describes the various elements of the Function Arguments dialog box.

The required argument names appear in bold text. Optional argument fields Enter the desired optional arguments for the function. The optional argument names appear in non-bold text.

Collapse Dialog button Minimize the Function Arguments dialog box to graphically select cell and range references directly on your worksheets. This command appears in numerous dialog boxes, wherever you have the option of manually selecting a cell or range. Once you collapse a dialog box, this becomes the Expand Dialog button, which you can use to restore the dialog box to its full size. Function description View a brief description of the function.

Argument description View a brief description of the currently selected argument. Formula result Preview the function result given the currently entered arguments. Graphical Cell and Range Reference Entry Graphical Cell and Excel provides you with a useful graphical method for entering cell and range references for a Range Reference Entry number of purposes, including for use in formulas and functions. Whenever you need to enter a cell or range reference, you have the option of typing the reference manually or selecting the reference graphically right from the worksheet.

This is why the Function Arguments dialog box provides the Collapse Dialog buttons. To graphically select a reference, you can simply select the cell or the range with mouse clicks or by dragging the mouse or by using the appropriate equivalent action on a touchscreen device , and then either press the Enter key or select the Expand Dialog button in the minimized dialog box.

Note: If you are manually typing a formula or function, once you've graphically selected a range, you can simply enter the next formula element or type a comma and then enter the next function argument. After you graphically select a cell or range reference, Excel displays the marquee selection to help you verify that you have selected the correct cell or range.

When you have finished entering the reference, the marquee selection goes away. Figure Graphically selecting cell and range references is a quick and easy alternative to typing them.

As such, Excel provides you with a fast and easy way to do this: the AutoSum feature. The AutoSum feature enables you to calculate the total of the values in a row or a column simply by selecting a single button and then pressing Enter or Tab. The AutoSum feature will automatically try to guess which cells you would like to add together if you use it on a cell in a row or a column that contains values.

AutoSum first looks for cells above, then looks for cells to the left of the active cell. You can also manually edit the group of cells or the range that the AutoSum feature should include as arguments in the SUM function. If there are no values in the row and the column associated with a cell and you insert the SUM function, you must manually enter the arguments in the SUM function. For ease of access purposes, Excel also displays the AutoSum button in the Editing group on the Home tab.

When you select the AutoSum drop-down arrow, Excel displays a menu that allows you to insert one of these other common functions into the active cell.

Figure The other commonly used functions available from the AutoSum button. Consider demonstrating You can use the functions available from the AutoSum drop-down arrow to perform the following simple examples of how tasks.

COUNT Find the number of cells, out of those that have been specified in the arguments, that contain numeric entries. MAX Find the largest single numeric value out of all of the values entered in the cells specified in the arguments. MIN Find the smallest single numeric value out of all of the values entered in the cells specified in the arguments. A Basic Function Syntax function's syntax is simply the structure necessary to properly express the function and to define its Explain to the students arguments.

As stated earlier, all Excel functions begin with an equal sign followed by the function the importance of fully name. The function name is followed by a set of parentheses that contains the function's arguments; understanding basic the arguments are separated by commas. Remember that depending on the particular function you function syntax, as this are using, arguments can include constants, cell or range references, logical values such as TRUE or will form the basis for FALSE, formulas, and even other functions.

Figure The basic elements of an Excel function. Functions can contain both required and optional arguments. In function syntax, optional arguments are notated by using square brackets [ ]. The SUM function, as an example, contains one required argument and up to optional arguments. Excel must have at least one number to return a result.

Excel can sum up to values with a single SUM function. So the first argument value is required, and the rest are optional. Each function has a unique syntax and requires different specific arguments. If all of the arguments for a function are valid, the function will return a result in a cell.

If one or more of the arguments in a function are invalid, Excel will return an error, which you will have to correct.

Let's take a look at the syntax for each of the commonly used functions mentioned so far. Note: The ellipsis In this case, it's up to arguments, which is the maximum number of supported arguments in an Excel function. Description: Adds the values specified by the arguments. For this function, the arguments can be constants, cell or range references, or both.

The following table includes examples of the SUM function in action. Description: Counts the number of cells specified in the arguments that contain a numeric entry. For this function, the arguments can be cell or range references, or both. However, it is more intended to count numeric cell entries. Description: Returns the largest numeric value out of all numeric values in the arguments. The following table includes examples of the MAX function in action.

Description: Returns the smallest numeric value out of all numeric values in the arguments. The following table includes examples of the MIN function in action.

When you type an equal sign into a cell, and then begin typing the name of a function, watch it themselves as a Excel displays a pop-up menu with all of the available functions that begin with the characters you supplement to your instruction. If not, please have already typed. This allows you to select the desired function, and then simply enter the required remind students to visit arguments to complete it.

The Formula AutoComplete feature also displays a tooltip that describes the LearnTOs for this whatever function you select from the pop-up menu. It can also help you quickly select a function when you can't remember its exact name. The Formula AutoComplete Feature Figure Excel displays the available function options and their descriptions as you type the function name. The Formula AutoComplete feature also provides you with assistance when entering function The Arguments Tooltip arguments.

As you enter the arguments for the selected formula, Excel displays a different tooltip that highlights the specific argument you're currently entering. This helps you keep track of which arguments you've entered and which ones you still need to enter. When you're done entering all necessary arguments, simply press Enter or Tab, and Excel will automatically add the closing parenthesis and enter the function. Scenario You've launched a sales contest to reward Develetech's best performing sales reps.

To track their progress, you created a worksheet to store and perform calculations on the sales figures for each of the reps. You're trying to analyze the numbers to determine which sales reps will win prizes. You want to start by calculating each rep's total sales, average quarterly sales, and highest and lowest quarterly sales.

This activity exposes students to the many 1. Open the Sales Contest. Calculate the total sales for the first two sales reps: Silva and Maddox. You may wish to let students a Select cell F4. Remind students that, d Press Enter. Calculate the average quarterly sales for the same two sales reps. Although Excel e Perform the same calculation for Maddox. Determine the highest quarterly sales total for the same two sales reps. Note: You may need to move the Insert Function dialog box to select the range.

Determine the lowest quarterly sales for the same two sales reps. You'll likely need to apply the same formula or function to each row. If your organization employs hundreds, or even thousands, of sales associates, this could be an incredibly lengthy, tedious process. Being able to create one set of formulas or functions and then apply it to every row would be far easier. Excel, much like a word-processing application, provides you with a number of methods to reuse nearly any of your content, including formulas and functions, basically by using a variety of copy and paste techniques.

This functionality represents one of the most useful time-saving features of Excel. By reusing your content, you can eliminate the hassle and reduce the likelihood of entering numerous errors in your workbooks. All of this will allow you to avoid repetitive tasks and hours of troubleshooting so you can focus on more important tasks.

Formulas and the Cut, Copy, and Paste Commands As with other worksheet content, if you want to reuse your Excel formulas and functions, you can do so by using the Cut, Copy, and Paste commands. By default, if a cell contains a formula or a function and you cut or copy its content to the clipboard, when you paste the content to another cell, Excel pastes the formula in the destination cell. Although the destination cell will display the result of the formula or function, as it did in the source cell, the content is still the formula.

This functionality forms the basis of how you can reuse formulas and functions throughout your worksheets and workbooks. Drag-and-Drop Editing In addition to using the Cut, Copy, and Paste commands to move content, you can use Excel's drag-and-drop editing functionality.

When you select a cell or a range, the cell or range is displayed with a solid green border around it. If you place the mouse pointer over the green border anywhere other than above the fill handle, Excel displays the mouse pointer as a move icon. When the move pointer appears, you can click and drag the selected cell or range and drop it in place anywhere else on the worksheet. This effectively cuts and pastes all content within the selection to the new location.

If you press and hold down the Ctrl key while performing this procedure, the mouse pointer changes into the copy pointer. By using the copy pointer, you can drag a copy of the content in the selected cell s to a new location. This is the same as copying and pasting the content. The Paste Options Excel provides you with a number of different options for pasting copied content into other The Paste Options cells.

This is because there will be occasions where you want to paste certain elements of a cell's content into another cell, but not the exact contents. Click to perform a descending sort Z to A or largest number to smallest.

In the Order list, select the order that you want to apply to the sort operation — alphabetically or numerically ascending or descending that is, A to Z or Z to A for text or lower to higher or higher to lower for numbers.

For more information about how to sort data, see Sort data in a range or table. Click the arrow in the column header to display a list in which you can make filter choices. To select by values, in the list, clear the Select All check box. This removes the check marks from all the check boxes. Then, select only the values you want to see, and click OK to see the results. For more information about how to filter data, see Filter data in a range or table. Under Save As , pick where to save your workbook, and then browse to a folder.

Preview the pages by clicking the Next Page and Previous Page arrows. The preview window displays the pages in black and white or in color, depending on your printer settings. On the File tab, choose Options , and then choose the Add-Ins category.

Near the bottom of the Excel Options dialog box, make sure that Excel Add-ins is selected in the Manage box, and then click Go. In the Add-Ins dialog box, select the check boxes the add-ins that you want to use, and then click OK. If Excel displays a message that states it can't run this add-in and prompts you to install it, click Yes to install the add-ins.

For more information about how to use add-ins, see Add or remove add-ins. Excel allows you to apply built-in templates, to apply your own custom templates, and to search from a variety of templates on Office.

For more information about how to find and apply templates, see Download free, pre-built templates. Get started. Click File , and then click New.

Under New , click the Blank workbook. Click an empty cell. Type text or a number in the cell. Press Enter or Tab to move to the next cell. Select the cell or range of cells that you want to add a border to. Select the cell or range of cells that you want to apply cell shading to. Select the cell to the right or below the numbers you want to add. AutoSum adds up the numbers and shows the result in the cell you selected. That tells Excel that this cell will contain a formula.

Press Enter. This runs the calculation. Select the cells that have numbers you want to format. Pick a number format. Select your data by clicking the first cell and dragging to the last cell in your data. To use the keyboard, hold down Shift while you press the arrow keys to select your data. Click OK. Select the cells that contain numbers you want to add or count. Select the data you want to examine more closely. When you like what you see, click that option.

Select the cells that contain the data you want to show in a chart. To quickly sort your data Select a range of data, such as A1:L5 multiple rows and columns or C1:C80 a single column. Select a single cell in the column on which you want to sort. To sort by specific criteria Select a single cell anywhere in the range that you want to sort. The Sort dialog box appears. In the Sort by list, select the first column on which you want to sort. Select the data that you want to filter.

If this is the first time you've save this file: Under Save As , pick where to save your workbook, and then browse to a folder. In the File name box, enter a name for your workbook. Click Save. Click Print. Need more help? Expand your skills. Get new features first. Was this information helpful?

Yes No. Thank you! Any more feedback?