Discuss how sorting, filtering, conditional formatting, and subtotaling can help

Question

Discuss how sorting, filtering, conditional formatting, and subtotaling can help you make effective decisions based on the information you have on hand (data)? Be very specific!
Of the different database functions learned this week (DAVERAGE, DCOUNT, DMAX, DMIN, DSUM, SUMIF, COUNTIF, MATCH, and INDEX), which of these do you see yourself using at work or home? Tell us how, why, and when you use them. Discuss how sorting, filtering, conditional formatting, and subtotaling can help you make effective decisions based on the information you have on hand (data)? Be very specific!
Of the different database functions learned this week (DAVERAGE, DCOUNT, DMAX, DMIN, DSUM, SUMIF, COUNTIF, MATCH, and INDEX), which of these do you see yourself using at work or home? Tell us how, why, and when you use them. Discuss how sorting, filtering, conditional formatting, and subtotaling can help you make effective decisions based on the information you have on hand (data)? Be very specific!
Of the different database functions learned this week (DAVERAGE, DCOUNT, DMAX, DMIN, DSUM, SUMIF, COUNTIF, MATCH, and INDEX), which of these do you see yourself using at work or home? Tell us how, why, and when you use them.

Explanation / Answer

Sorting

Sorting data is an integral part of Data Analysis. You can arrange a list of names in alphabetical order, compile a list of sales figures from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.You can sort by columns or by rows. Most of the sorts that you use will be column sorts.You can sort data in one or more columns by

Sort criteria for a table are saved with the workbook such that you can reapply the sort to that table every time you open the workbook. Sort criteria are not saved for a range of cells. For multicolumn sorts or for sorts that take a long time to create, you can convert the range to a table. Then, you can reapply the sort when you open a workbook.

Filtering

Filtering allows you to extract data that meets the defined criteria from a given Range or table. This is a quick way to display only the information that is needed by you.You can Filter data in a Range, table or PivotTable.You can filter data by

In a table, the column headers are automatically tagged to filters, known as AutoFilters. AutoFilter is represented by the arrow next to column header. Each AutoFilter has filter options based on the type of data you have in that column. For example, if the column contains numbers, when you click on the arrow next to the column header, Number Filter Options appear.When you click a Filter option or when you click on Custom Filter that appears at the end of the Filter options, Custom AutoFilter dialog box appears, wherein you can customize your filtering options.In case of a Range, you can provide the column headers in the first row of the range and click on filter in the Editing group on Home tab. This will make the AutoFilter on for the Range. You can remove the filters that you have in your data. You can also reapply the filters when data changes occur.

conditional formatting

In Microsoft Excel, you can use Conditional Formatting for data visualization. You have to specify formatting for a cell range based on the contents of the cell range. The cells that meet the specified conditions would be formatted as you have defined.

Example

In a range containing the sales figures of the past quarter for a set of salespersons, you can highlight those cells representing who have met the defined target, say, $2500.You can set the condition as total sales of the person >= $2500 and specify a color code green. Excel checks each cell in the range to determine whether the condition you specified, i.e., total sales of the person >= $2500 is satisfied.Excel applies the format you chose, i.e. the green color to all the cells that satisfy the condition. If the content of a cell does not satisfy the condition, the formatting of the cell remains unchanged. The result is as expected, only for the salespersons who have met the target, the cells are highlighted in green – a quick visualization of the analysis results.You can specify any number of conditions for formatting by specifying Rules. You can pick up the rules that match your conditions from

You can also define your own rules. You can

Further, you have several formatting options in Excel to choose the ones that are appropriate for your Data Visualization

subtotaling

SUBTOTAL formula is used to find out subtotal of a given range of cells. You give SUBTOTAL two things – (1) a range of data (2) type of subtotal. In return, SUBTOTAL will give you the subtotal for that data.Unlike SUM, AVERAGE, COUNT etc. which do one thing and only one thing, SUBTOTAL is versatile. You can use it to sum up, average, count a bunch of cells.

Here is the SUBTOTAL syntax:   =SUBTOTAL (TYPE OF TOTAL, RANGE OF CELLS)

(2) Part

Dsum, Dmin, Dmax, Daverage, and Dcount. Each of these "D" functions performs a calculation over a range, but only on the records that match the criteria in the criteria range. In all of the following examples, the arguments are:

DSum(database, column, criteria range).

Sums all of the values in the given column of the data range that meet the criteria specified in the criteria range.

DMin(database, column, criteria range).

Finds the minimum of the values in the given column of the data range that meet the criteria specified in the criteria range.

DMax(database, column, criteria range).

Finds the maximum of the values in the given column of the data range that meet the criteria specified in the criteria range.

DAverage(database, column, criteria range).

Finds the average of the values in the given column of the data range that meet the criteria specified in the criteria range.

DCount(database, column, criteria range).

Counts the number of cells in the given column of the data range that meet the criteria specified in the criteria range.

The Sum functions total the values in a range:

Sum(range)               Sums all of the numbers in the range

SumIf(range, criteria) Sums all of the numbers in the range that meet the specified criteria

CountIf(range, criteria)        Counts all of the cells that match a specified condition

INDEX function. Simply put, INDEX takes a cell range and returns a cell within that range based on a count provided by the user. The formula looks like this:

The MATCH function returns the position of a cell within an array by matching against a criteria string. The formula looks like this:

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.