Basic Excel: Working with Formulas and Functions In Microsoft Excel, you can use Conditional Formatting for data visualization. 9.5 References https://exceljet.net/lessons/conditional-formatting-formula-in-a-table https://edu.gcfglobal.org/en/excel2016/conditional-formatting/1/ https://www.ablebits.com/office-addins-blog/2014/06/10/excel- conditional-formatting-formulas/comment-page-6/ https://www.tutorialspoint.com/excel_data_analysis/excel_data_analysis_ conditional_formatting.htm Page 51 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 10: UNDERSTANDING AND USING ARRAY FORMULAS Structure 10.0 Learning Objectives 10.1 Introduction 10.2 Array 10.3 Array formulas 10.4 Entering Array formula in Excel 10.5 Summary 10.6 References 10.0 Learning Objectives After studying this unit, you will be able to: Explain What is Array in MS Excel Explain are Array formula MS Excel Explain how to enter array in MS Excel 10.1 Introduction Array formulas in Excel are an extremely powerful tool and one of the most difficult to master. A single array formula can perform multiple calculations and replace thousands of usual formulas. And still, 90% of users have never used array functions in their worksheets simply because they are scared to start learning them. Indeed, array formulas one of the most confusing Excel features to learn. The aim of this tutorial is to make the learning curve as easy and smooth as possible. 10.2 what is an Array in Excel? Before we start on array functions and formulas, let's figure out what the term \"array\" means. Essentially, an array is a collection of items. The items can be Page 52 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions text or numbers and they can reside in a single row or column, or in multiple rows and columns. For example, if you put your weekly grocery list into an Excel array format, it would look like: {\"Milk\", \"Eggs\", \"Butter\", \"Corn flakes\"} Then, if you select cells A1 through D1, enter the above array preceded by an equal sign (=) in the formula bar and press CTRL + SHIFT + ENTER, you will get the following result: 10.3 What is Array Formula in Excel? The difference between an array formula and a regular formula is that an array formula processes several values instead of just one. In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula. Not only can an array formula deal with several values simultaneously, it can also return several values at a time. So, the results returned by an array formula is also an array. Array formulas are available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower. And now, it seems to be the right time for you to create your first array formula. Simple example of Excel array formula Suppose you have some items in column B, their prices in column C, and you want to calculate the grand total of all sales. Of course, nothing prevents you from calculating subtotals in each row first with something as simple as =B2*C2 and then sum those values: Page 53 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions However, an array formula can spare you those extra key strokes since it gets Excel to store intermediate results in memory rather than in an additional column. So, all it takes is a single array formula and 2 quick steps: Select an empty cell and enter the following formula in it: =SUM(B2:B6*C2:C6) Press the keyboard shortcut CTRL + SHIFT + ENTER to complete the array formula. Once you do this, Microsoft Excel surrounds the formula with {curly braces}, which is a visual indication of an array formula. What the formula does is multiply the values in each individual row of the specified array (cells B2 through C6), add the sub-totals together, and output the grand total: 10.4 Entering Array Formula In Excel How to enter array formula in Excel (Ctrl + Shift + Enter) The combination of the 3 keys CTRL + SHIFT + ENTER is a magic touch that turns a regular formula into an array formula. When entering an array formula in Excel, there are 4 important things to keep in mind: Page 54 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Once you've finished typing the formula and simultaneously pressed the keys CTRL SHIFT ENTER, Excel automatically encloses the formula between {curly braces}. When you select such a cell(s), you can see the braces in the formula bar, which gives you a clue that an array formula is in there. Manually typing the braces around a formula won't work. You must press the Ctrl + Shift + Enter shortcut to complete an array formula. Every time you edit an array formula, the braces disappear and you must press Ctrl + Shift + Enter again to save the changes. If you forget to press Ctrl + Shift + Enter, your formula will behave like a usual formula and process only the first value(s) in the specified array(s). Because all Excel array formulas require pressing Ctrl + Shift + Enter, they are sometimes called CSE formulas. 10.5 Summary A single array formula can perform multiple calculations and replace thousands of usual formulas. Essentially, an array is a collection of items. The items can be text or numbers and they can reside in a single row or column, or in multiple rows and columns. an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula. 10.6 References https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas- functions-excel/ https://support.microsoft.com/en-us/office/guidelines-and-examples-of- array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 https://www.vertex42.com/blog/excel-formulas/array-formula- examples.html https://exceljet.net/glossary/array-formula Page 55 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions CHAPTER - 11: MAKING YOUR FORMULAS ERROR-FREE Structure 11.0 Learning Objectives 11.1 Introduction 11.2 Identifying and correcting common formula errors 11.3 Using Excel auditing tools 11.4 Using formula AutoCorrect 11.5 Tracing cell relationships 11.6 Checking spelling and related features 11.7 Summary 11.8 References 11.0 Learning Objectives After studying this unit, you will be able to: Explain Identifying and correcting common formula errors in MS Excel? Explain auditing tools in MS Excel Explain formula autocorrect in MS Excel Explain tracing cell relationships in MS Excel Explain checking spelling and related features 11.1 Introduction It goes without saying that you want your Excel worksheets to produce accurate results. Unfortunately, it's not always easy to be certain that the results are correct, especially if you deal with large, complex worksheets. This chapter introduces the tools and techniques available to help identify, correct, and prevent errors. Page 56 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 11.2 Finding and Correcting Formula Errors Making a change in a worksheet — even a relatively minor change — may produce a ripple effect that introduces errors in other cells. For example, accidentally entering a value into a cell that previously held a formula is all too easy to do. This simple error can have a major impact on other formulas, and you may not discover the problem until long after you make the change — if you discover the problem at all. Formula errors tend to fall into one of the following general categories: Syntax errors: You have a problem with the syntax of a formula. For example, a formula may have mismatched parentheses, or a function may not have the correct number of arguments. Logical errors: A formula doesn't return an error, but it contains a logical flaw that causes it to return an incorrect result. Incorrect reference errors: The logic of the formula is correct, but the formula uses an incorrect cell reference. As a simple example, the range reference in a Sum formula may not include all the data that you want to sum. Semantic errors: An example is a function name that is spelled incorrectly. Excel will attempt to interpret it as a name and will display the #NAME? error. Circular references: A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Circular references are useful in a few cases, but most of the time, a circular reference indicates a problem. Array formula entry error: When entering (or editing) an array formula, you must press Ctrl+Shift+Enter to enter the formula. If you fail to do so, Excel doesn't recognize the formula as an array formula, and you may get an error or incorrect results. Incomplete calculation errors: The formulas simply aren't calculated fully. To ensure that your formulas are fully calculated, press Ctrl+Alt+Shift+F9. Syntax errors are usually the easiest to identify and correct. In most cases, you'll know when your formula contains a syntax error. For example, Excel won't permit you to enter a formula with mismatched parentheses. Other syntax errors also usually result in an error display in the cell. Page 57 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Mismatched parentheses In a formula, every left parenthesis must have a corresponding right parenthesis. If your formula has mismatched parentheses, Excel usually won't permit you to enter it. An exception to this rule involves a simple formula that uses a function. For example, if you enter the following formula (which is missing a closing parenthesis), Excel accepts the formula and provides the missing parenthesis: =SUM(A1:A500 A formula may have an equal number of left and right parentheses, but the parentheses may not match properly. For example, consider the following formula, which converts a text string such that the first character is uppercase and the remaining characters are lowercase. This formula has five pairs of parentheses, and they match properly: =UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1) The following formula also has five pairs of parentheses, but they're mismatched. The result displays a syntactically correct formula that simply returns the wrong result: =UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1)) Often, parentheses that are in the wrong location will result in a syntax error, which is usually a message that tells you that you entered too many or too few arguments for a function. Tip Excel can help you with mismatched parentheses. When you're editing a formula and you move the cursor over a parenthesis, Excel displays it (and its matching parenthesis) in bold for about one-half second. In addition, Excel color-codes pairs of nested parentheses while you're editing a formula. 11.3 Using Formula Autocorrect When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction. The accompanying figure shows an example of a proposed correction. Page 58 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Be careful when accepting corrections for your formulas from Excel because it doesn't always guess correctly. For example, I entered the following formula (which has mismatched parentheses): =AVERAGE(SUM(A1:A12,SUM(B1:B12)) Excel then proposed the following correction to the formula: =AVERAGE(SUM(A1:A12,SUM(B1:B12))) You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula is syntactically correct, but it's not what I intended. The correct formula is =AVERAGE(SUM(A1:A12),SUM(B1:B12)) Some more errors are: Cells are filled with hash marks, blank cells are not blank,extra space characters, formulas returning an error, tracing Error Values, #DIV/0! Errors, #N/A errors, #NAME? errors, #NULL! Errors, #NUM! errors, #REF! errors, #VALUE! Errors, Absolute/relative reference problems, etc. 11.4 Using Excel Auditing Tools Excel includes a number of tools that can help you track down formula errors. This section describes the auditing tools built in to Excel. Page 59 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions Identifying cells of a particular type The Go to Special dialog box (shown in Figure 31.6) is a handy tool that enables you to locate cells of a particular type. To display this dialog box, choose Home => Editing =>Find & Select => Go to Special. You can use the Go to Special dialog box to select cells of a certain type, which can often help you identify errors. For example, if you choose the Formulas option, Excel selects all the cells that contain a formula. If you zoom the worksheet out to a small size, you can get a good idea of the worksheet's organization. 11.5 Tracing Cell Relationships To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts: Cell precedents: Applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn't used directly in the formula but is used by a cell that you refer to in the formula. Cell dependents: These formula cells depend upon a particular cell. A cell's dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent. For example, consider this simple formula entered into cell A4: Page 60 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions =SUM(A1:A3) Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 all have at least one dependent cell (cell A4). Identifying cell precedents for a formula cell often sheds light on why the formula isn't working correctly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For example, if you're about to delete a formula, you may want to check whether it has any dependents. 11.6 Checking Spelling and Related Features Using the background error-checking feature: Some people may find it helpful to take advantage of the Excel automatic error- checking feature. This feature is enabled or disabled via the Enable Background Error Checking check box, found on the Formulas tab of the Excel Options dialog box. In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check. When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a drop-down control appears. Clicking this drop-down control provides you with options. The options that appear when you click the drop-down control in a cell that contains a #DIV/0! error. The options vary, depending on the type of error. Page 61 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions In many cases, you'll choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored errors can be reset so that they appear again. (Use the Reset Ignored Errors button on the Formulas tab of the Excel Options dialog box.) You can choose Formulas image Formula Auditing image Error Checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. This dialog box is modeless; that is, you can still access your worksheet when the Error Checking dialog box is displayed. Excel provides another way to evaluate a part of a formula: Page 62 of 63 All Rights Reserved. Vol. TLE001/03-2022
Basic Excel: Working with Formulas and Functions 1. Select the cell that contains the formula. 2. Press F2 to get into Cell Edit mode. 3. Use your mouse to highlight the portion of the formula you want to evaluate. Or press Shift and use the navigation keys. 4. Press F9. The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc to cancel and return your formula to its previous state. 11.7 Summary Making a change in a worksheet — even a relatively minor change — may produce a ripple effect that introduces errors in other cells. Syntax errors are usually the easiest to identify and correct. When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction. Cell dependents are the formula cells depend upon a particular cell. Cell precedents are applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. 11.8 References https://apprize.best/microsoft/excel_9/31.html https://exceljet.net/excel-formula-errors https://www.exceltip.com/excel-errors/formula-errors-in-excel-and- solutions.html https://www.datamation.com/applications/error-free-worksheets-in-excel- 2007/ https://support.microsoft.com/en-us/office/detect-errors-in-formulas- 3a8acca5-1d61-4702-80e0-99a36a2822c1 Page 63 of 63 All Rights Reserved. Vol. TLE001/03-2022
Search
