Want to create or adapt books like this? Learn more about how Pressbooks supports open publishing practices.
Excel Chap 2 – Beyond the Basics
Absolute versus static references, understanding complex formulas, date & time functions, logical functions, more math functions, sorting and filtering, conditional formatting, bar & column charts, line charts.
Cell referencing is an extremely useful feature when formulas need to be copied across ranges in a worksheet. When creating formulas that contain references to cells or cell ranges, the default cell reference is considered to be a relative cell reference. When formulas with these type of cell references are copied to other cells the formulas will automatically change relative to the cells that they are copied to. However, there may be instances where it is necessary for Excel to keep the exact cell referenced in a formula when copying to other cells. A cell reference that does not change when copied is called an absolute cell reference (sometimes called a fixed cell reference). The indicator that a cell reference is absolute is the presence of a dollar sign symbol in front of both the column letter and row number, such as $A$3. To create an absolute cell reference, either type the dollar sign manually, or press the F4 after entering the cell reference. Repeatedly pressing the F4 key cycles the reference through the four different combinations of relative, absolute, and mixed cell references.
Mixed cell references are a combination of relative and absolute: either the column is relative and the row fixed (absolute), for example D$2, or the column is fixed and the row relative: $D2. Mixed cell references are rarely used, but they play a significant role when it is necessary to keep a single row or column unchanged while copying the formula. Mixed cell references are commonly used when creating a table of values, like a multiplication table or a mortgage rate table.
Formulas can be simple mathematical formulas or complicated formulas involving multiple mathematical operations, multiple cell ranges and nested functions. While it is a good idea to remember the order of operations rules, complex formulas can use parentheses to identify the arguments of functions and to override the order of operations. In mathematical operation formulas, operations within parentheses are performed before those outside of it. For example, in =A3+B3*C3, B3 is multiplied by C3 before A3 is added to the result, but in =(A3+B3)*C3, A3 and B3 are added together first, then the result is multiplied by C3.
The TODAY and NOW functions are very similar since both return the current system date. However, the NOW function also returns the current system time. Many users format the results of the NOW function to only display the current system time. One characteristic of these functions versus the DATE function is that TODAY and NOW are volatile functions, so if the workbook is opened tomorrow, these functions will update automatically, whereas the DATE function will not change. These functions are for use within the grid of the worksheet, typically to calculate durations. There are separate Date and Time fields for use in worksheet headers and footers.
Many employers and educators are placing increasing emphasis on the value of critical thinking. Critical thinking skills are often associated with problem solving skills, empathy, and thinking and acting in purposeful ways by making fact-based decisions. Excel is a practical tool for collecting and analyzing data critically. Excel’s logical functions are especially useful for enabling critical thinking. However, before exploring the utilization of Excel’s logical functions, it is necessary to review the logical operators that are used to compare data between cells. The operators are often referred to as comparison operators. The six main logical operators are explained in the following graphic:
These comparison operators can be used in Excel functions to compare all types of data, including dates, numbers, Boolean values (True or False, Yes or No), or text. All arguments must evaluate to the Boolean values of either True or False, or contain another, nested logical function.
The IF function is one of the most popular and useful functions in Excel. It can be used to make a decision based on a comparison. If the comparison is true, one value is displayed in the cell; if the comparison is false, a second value is displayed. The syntax of the IF function is: IF(logical_test, [value_if_true], [value_if_false]).
- logical_test – a value or logical expression that can be either TRUE or FALSE. Required.In this argument, you can specify a text value, date, number, or any comparison operator.
- value_if_true – the value to return when the logical test evaluates to TRUE, i.e. if the condition is met. Optional.
- value_if_false – the value to be returned if the logical test evaluates to FALSE. Optional.
Instead of returning text strings, the IF formula can test the specified condition, perform a corresponding math operation and return a value based on the result. This is done by using arithmetic operators or other Excel functions in the value_if_true and /or value_if_false arguments. For example, =IF(C4>=D4, E4*.25, E4*10%). This formula is determining if the value in column C is greater than or equal to the value in column D. If the result of the comparison is true, then the value to be displayed should be the value in column E multiplied by .25 (25%). If the result of the comparison is false, then the value to be displayed should be the value in column E multiplied by 10% (.10).
Other popular Excel Logical functions include the AND , as well as the OR functions. These functions allow users to create multiple logical tests within a single IF function. These functions return either TRUE or FALSE results when their arguments are evaluated. The AND function only returns TRUE if every condition is met. However, the OR function returns TRUE if any condition is met. The syntax of both functions are the same: =AND(logical1, logical2, etc.) Examples of each function are illustrated in the screenshots below. The first illustration uses the AND function to determine if any of the people are eligible for the Inner Circle. They need to have a Customer Satisfaction Score of at least 4.0, and be a Vikings fan. The second example uses the OR function. The more lenient comparison operator yields more eligible people for the Inner Circle.
Practice 7: Expense Report
- Date : 5/30/2019
- Description : Travel to Minneapolis
- Mileage : 205
- Lodging : 150
- In cell G9 enter the following complex formula using relative and absolute cell references: =C9+(D9*$C$6)+E9+F9 (are the parenthesis necessary?)
- Format columns C, E, F and G as Currency format with two decimals.
- Date : 5/31/2019
- Description : Travel home
- Use the fill handle to copy the formula in G9 to G10.
- Insert a new row below the Mileage Rate row, and in B6 enter the label: Trip Duration. (bold the label)
- In cell C6 enter a formula that subtracts A9 from A10. Change the format of cell C6 to General.
- In cell B15 enter the label: Totals: (right-align & bold this cell).
- Select cells C15:G15 and click the AutoSum button. Change the format of D15 to General.
- Enter the label: Need Receipts? in cell B2. (bold the label)
- In cell C2, enter the following logical function: =IF(F15>0,”Yes”,”No”)
Worksheets can have large amounts of data, and it can be overwhelming if it is not sorted correctly. Arranging data in a specified order is called sorting . Rows can be sorted in either ascending (low to high) or descending (high to low) order. Ascending order can also be considered alphabetic order if the data is text or chronological order if the data is dates or times.
Before data can be sorted, Excel needs to know the exact range of cells that is to be sorted. Excel will select areas of related data as long as th ere are no blank rows or columns in the data range. Blank rows and columns define the outer limits of the data range. To ensure that the correct data is selected, highlight the range before starting the sort.
As Excel determines the defined range of data to be sorted, Excel also determines if field names exist. Users should format the first row of the data range with unique formatting Often, just making the labels bold will help Excel identify the field names. Identifying the field names will prevent Excel from including these fields in the records to be sorted. The field names become the sort keys needed for multiple column sorts. In the image below, the possible sort keys are: Cruise Date, Accommodation Category, Rooms Available, Rooms Rented, Rate, Revenue and Shortfall.
To remove a single filter, click the filter icon in the row header and choose Clear Filter from…. To clear all filters quickly, choose the Clear button from the Data tab on the ribbon or to remove filtering functionality, re-click the Filter icon from the Sort & Filter group. For more advanced users there are many more ways to utilize filters in Excel, such as Custom Filters and Advanced Filters. Nonetheless, a lot can be accomplished with the basic AutoFilter feature.
The icon sets work great when color is not effective, such as when printing to black & white laser printers. Icons are even more noticeable than colors because of their unique look and sparing use. Icon sets will help visually represent the data with directional arrows, shapes, indicators, ratings, and other objects. For icons in sets of three, Excel will assign icons by dividing values into thirds – the first icon is assigned to the top one third of values, the second icon is assigned to the second third of values, and the third icon is assigned to the lowest one third of values. The values are adjusted for four and five-icon sets. Conditional formatting’s flexibility is extensive, but perhaps even more impressive is that, like formulas, conditional formatting rules are volatile. As the data changes, the conditional formatting dynamically adjusts to evaluate the new data against the existing conditional rules.
With Excel, users can organize data so that it has context and meaning. The output of Excel manipulation can take the shape of either a table or chart. When deciding to use a chart or a table to communicate the data-driven message, it is wise to always ask how the information will be used, and who is the audience? People interact very differently with these two types of visuals. Tables , which display data in a columnar layout, are meant to be read. Therefore tables are ideal when the data requires more specific analysis. Tables offer preciseness, letting users dive deeper to crunch the numbers and examine exact values, instead of focusing on approximations or visualizations.
A chart is a visual representation of a range of related worksheet data. A chart can illustrate a large amount of numerical data quickly and in an easy-to-understand fashion. Charts are particularly useful for simplifying complex sets of data to expose the shape of the data – numerical patterns, trends, and other significant activity. What charts lack in terms of precision they overcome with broader insight and quicker comprehension.
Learning how to work with charts means not only knowing how to create them but also realizing that different types of chart and layouts can reveal and emphasize different knowledge. Like a picture, a chart can be worth a thousand words. It all depends on how well the chart is designed and developed. A well constructed chart will provide context and clarity for data analysis and story telling. The first step to creating useful charts it to organize and precisely select the data that should be charted. This includes the cells that contain both the data and the label headings.
Each chart type conveys information in different ways. Pie charts , for example, are best for charting data that is a percentage of a whole. For example, what percentage of your monthly income is spent on transportation? Charts contain different objects for each chart type. Objects in a pie chart include:
- Chart title – describes what is charted. The label heading of the data series is used by default, but this can be edited.
- Legend – an index of information that corresponds to the category labels.
- Data labels – identify each value in the data series.
- Plot area – the part of the chart that displays data graphically.
- Chart area – the entire chart and all of its elements.
Which pie chart looks better? Generally, removing the legend through use of data labels is considered a preferable design strategy. Alas, there are even more ways to customize a pie chart that is beyond the scope of this text! Additional functionality includes creating 3-D pie charts, exploding slices, rotating, sorting, adding different fill designs, and much more!
Bar charts are practical for comparing the differences between values with horizontal bars. The lengths of the bars are proportional to the size of the data category they represent. A vertical bar chart is referred to as a column chart, and they are basically the same – just 90° apart. Unlike a pie chart, a bar chart can include several series of data. Bar charts are simple, easy and flexible, and thanks to the horizontal layout, bar charts can accommodate longer category names. Bar (and column) charts contain the following objects:
- Chart title – describes what is charted.
- Plot area – the region within the horizontal and vertical axes.
- Axes titles (horizontal, vertical)- describe the data.
- Legend – an index of information that corresponds to the series names.
- Data labels – identify each value in a data series.
- Value axis (y-axis) – contains values. Horizontal axis on bar charts and vertical axis on column charts.
- Category axis (x-axis) – contains the category labels. Horizontal axis on column charts and vertical axis on bar charts.
- Gridlines – mark the intervals on an axis.
While the data and chart above only displays one data series, most bar and column charts tend to display multiple data series to compare and contrast one set of data against another. The chart below adds a second data series in column C, and changes the chart type to a column chart. The two different data series are illustrated with different color data points. Also, note how the axes change directions! The value axis maximum, major and minor units were also modified to different numerical values.
In a Line Chart, the vertical axis (Y-axis) always displays numeric values and the horizontal axis (X-axis) displays time or other categories. Time intervals can be measured in years, months, days, or hours.
In the 2-D line chart illustrated above, two sets of data series are plotted for fifteen days. The Skinny Mints cookies are identified by the red line, and the orange line plots the Chocolate/Peanut Butter cookies. Each date is a data point. Additional data series can be added by expanding the data source range. Line charts provide an easy way to track historical data. In many cases, the way the data is trending makes it easy to predict the results of data in future periods.
Excel offers numerous other charts that might better help illustrate the story behind the data, however all charts must address the challenge of sharing or outputting the graphic(s) to their target audience(s). Here are a few printing issues to consider:
- Chart colors that are not compatible with black and white printers. A lot of office printers are black and white laser printers due to their low-cost to operate at high volumes. A color laser printer may be a worthy investment for common chart printing. If a black and white printer is all that is available, it may be necessary to change the colors to patterns or contrasting shades of grey.
- Not enough or too much information. Reads of the chart must be able to comprehend the message the chart is attempting to convey. This might require adding context, such as titles, data labels, and text-box callouts. Conversely, a chart can have too much information that the chart is cluttered and unreadable. In this case, summarizing the data my be prudent, or even re-selecting a different source data range. A chart can also be unreadable due to poor design considerations like wrong fonts and font sizes, as well as distracting colors and object sizes.
- Excel charts are volatile and will change as often as the source data is changed. If a static copy of a chart is needed, consider saving the chart as a picture. This can be accomplished through using the copy and paste special commands. Using Paste Special will allow a chart to pasted in either PNG, JPEG, GIF or other formats. Alternatively, Excel files can be saved as PDF files for inserting in other electronic documents/files.
Practice 8: Weather Report
- Open the data file The Weather Report.xlsx or create a new sheet from the illustration at right.
- Add conditional formatting to all cells in B6:E17 so that any cell is at least 90° with red fill cell formatting, or below 32° in light blue fill.
- Use the Internet to research average monthly high temps for Fort Myers, FL. Enter a label and the temps in column E. The conditional formatting should dynamically format the cells.
- Create a 2-D bar chart of Anchorage and Mesa temps for all twelve months.
- Move the bar chart to its own chart sheet.
- Return to the original source data sheet and create an embedded 2-D line chart with markers for all four cities for all twelve months. Change the chart title to Average Month High Temps. Move the chart slightly so the top-left edge is in cell F6.
- Save the file as The Weather Report2.xlsx to your hard drive.
Business Computers 365 Copyright © 2019 by Marcus Lacher is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License , except where otherwise noted.
Share This Book
Excel Templates | |
Click the links below to access the available for this chapter. |
To learn more about the book this website supports, please visit its . |
Copyright Any use is subject to the and | |
IMAGES
COMMENTS
SIA Computer Excel Chapter 2 Modified Critical Thinking. Perry decides that before either of his emplyees creates a new workbook he must give them a (n) __________.
This video is for students of the BUSN1360 Software Applications for Business class and demonstrates the steps for the Module 2: Excel Grader.Do you need to ...
A statistical function that counts the number of cells within a range that meet the given condition and which has two arguments- the range of cells to check and the criteria. Logical test. Any value or expression that can be evaluated as being true or false. IF function. A function that uses a logical test to check whether a condition is met ...
Excel is a practical tool for collecting and analyzing data critically. Excel's logical functions are especially useful for enabling critical thinking. However, before exploring the utilization of Excel's logical functions, it is necessary to review the logical operators that are used to compare data between cells.
PSY 201: The Psychology Major: Academic & Professional Issues Module 3: Technological Resources: Excel & Critical Thinking Study Guide Notes. Reading: Supplemental Materials on Excel (Review AVP online) McBurney: SEC 7 - 21. Module 3 Study Guide Objectives: Supplemental Materials on Excel You will learn: • What Excel is and what it is used for.
2-4d To Enter Formulas Using Point Mode. 1. With cell G4 selected, type "=0.26*" ( to begin the formula and then click cell F4 to add a cell reference in the formula. 2. - Type "-" (minus sign) and then click cell C4 to add a subtraction operator and a reference to another cell to the formula.
Click the links below to access the Excel Templates available for this chapter. Excel Template - Problem 4-8 (202.0K) Excel Template - Alternative Problem 4-2 (203.0K) Excel Template - Critical Thinking Case CP4-10 (215.0K) To learn more about the book this website supports, please visit its Information Center .
tune with the student of today. With an emphasis on Mac compatibility, critical thinking, and continual updates to stay in sync with the changing Microsoft Office 365, and by providing additional valuable assignments and resources, the Exploring series is able to offer you the most usable, current, and bene.
Critical Thinking Week 5 Student Name: After you have completed the Excel Chapter 1 and 2 assignments, work on the items below. Read the quotes on page 323 of your textbook. Do some research to answer the following questions: What is a business strategy? How do companies develop a strategy? Assume that one our Red Bluff Golf Course and Pro Shop ...
Learn how to preview and assign Critical Thinking Challenge assignments available to support Technology for Success. These "Choose Your Own Adventure"-style ...
Computer Science questions and answers. Shelly Cashman Excel 365/2021 | Module 2: SAM Critical Thinking Project 1c Roadrunner Online FORMAT WORKSHEETS GETTING STARTED Save the file SC_EX365_2021_CT2c_FirstLastName_1.xlsx as SC_EX365_2021_CT2c_FirstLastName_2.xlsx Edit the file name by changing "1" to "2".
Shelly Cashman Excel 365/2021 | Modules 1-3: SAM Critical Thinking Capstone Project 1c6. Format the row of data under the cell containing "Project Status Report" to show that it contains column headings. Change "Description" to Service Description as the complete column heading.
20 of 20. Quiz yourself with questions and answers for Critical Thinking Quiz Excel 2A and 2B Projects, so you can be ready for test day. Explore quizzes and practice tests created by teachers and students or create one from your course material.
I found that my students really enjoyed doing this Excel project in Computer Applications. Students will use Excel and its capabilities, to apply formulas and charts to a subject
Chapter 2 Arguments. Chapter 2. Arguments. The fundamental tool of the critical thinker is the argument. For a good example of what we are not talking about, consider a bit from a famous sketch by Monty Python's Flying Circus: 3. Man: (Knock) Mr. Vibrating: Come in.
Critical thinking is the discipline of rigorously and skillfully using information, experience, observation, and reasoning to guide your decisions, actions, and beliefs. You'll need to actively question every step of your thinking process to do it well. Collecting, analyzing and evaluating information is an important skill in life, and a highly ...
View Excel Chapter 2 copy from CGS 2100 at Tallahassee Community College. Excel Chapter 2 Solving Problems with Statistical Tools Review What is critical thinking? Formatting what things should
Order of Operations. Negation, percentages, exponentiations, multiplication, division, addition, subtraction. Point mode. Allows you to select cells for use in a formula by using the mouse. Average function. Sums numbers then divides by the number of values. Max function. That displays the highest value in a range.
This Video shows how to work with large Worksheets, charting, and What-If Analysis
A formula is an expression that calculates the value of a cell—and here calculation means something like 6+9. **A formula you have to manually type in a formula** **A formula, a user needs to be able to write down the calculation they want to perform A function, on the other hand, can also calculate things (usually more advanced calculations such as an average or a count of something).
View Critical Thinking Assignment Week 5a.docx from HDF MISC at Chemeketa Community College. Critical Thinking Week 5 After you have completed the Excel Chapter 1 and 2 assignments, work on the items ... Critical Thinking Week 5 After you have completed the Excel Chapter 1 and 2 assignments, work on the items below. Read the quotes on page 323 ...
Chapter 2 Key Terms Matching. Syntax. Click the card to flip 👆. A set of rules that governs the structure and components for properly entering a function. Click the card to flip 👆. 1 / 18.
View Critical Thinking Assignment_Excel.docx from MGMT 2301 at Southern Alberta Institute of Technology. Critical Thinking Assignment Microsoft Excel MGMT2301 Chiu School of Business Bow Valley ... CHAPTER 12 HW.docx. Solutions Available. University of Houston, Downtown. BA 3350. BSBCRT511 Project Portfolio.docx. Solutions Available. Queensland ...