Question

Write a brief explanation of each of these skills listed below. Explain how each of the following skills helps you transform raw data into meaningful information for users.

1. Use Conditional Functions
2. Do Conditional Formatting
3. Do Data Validation
4. Filter Data
5. Create a Pivot Table
6. Create a Chart
7. Create a Pivot Table and Chart
8. Use VLOOKUP

Answer 1

• Use Conditional Functions

A conditional formula in Excel is a formula that makes a logical test of data using the IF function. It essentially allows you to create a basic logical argument of “If (this), then (that).” Though there is an entire subset of philosophy devoted to truth-functional propositional logic, in this case, you don’t won’t have to open a textbook to develop logical arguments within Excel. One of the great benefits of using conditional formulas within Excel is that it’s pretty simple. Conditional programming is used in web design and development as well, particularly in the case where a website is visited using different browsers. I like to think of it as a point in which math, philosophy, and programming meet.

How to Write a Conditional Formula

The basic syntax of the IF formula in Excel is:

=IF(logical_test,[value_if_true],[value_if_false])

logical_test: the condition that you are checking for

[value_if_true]: the result you want if the condition is true

• Do Conditional Formatting

Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used. (By "default formatting", I mean the formatting that you set up using the normal formatting tools, not necessarily the worksheet's default font and font size.)

A cell can have up to 3 format conditions, each with its own formats, in addition to the default value of "no formatting". This allows you to have different formats depending on the value of the cell. For example, if the value was greater than 200, you can display the text in red, but if the value is between 100 and 200, display the text in green.  

Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations. Use caution when applying to to large ranges.

• Do Data Validation

Data validation is a feature in Excel used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters.

• Filter Data

Filter a range of data

1. Select any cell within the range.
2. Select Data > Filter.
3. Select the column header arrow .
4. Select Text Filters or Number Filters, and then select a comparison, like Between.
5. Enter the filter criteria and select OK.

• Create a Pivot Table

How to Create a Pivot Table

1. Enter your data into a range of rows and columns.
2. Sort your data by a specific attribute.
3. Highlight your cells to create your pivot table.
4. Drag and drop a field into the "Row Labels" area.
5. Drag and drop a field into the "Values" area.
6. Fine-tune your calculations.

• Create a chart

1. Select the data for which you want to create a chart.
2. Click INSERT > Recommended Charts.
3. On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look. ...
4. When you find the chart you like, click it > OK.

• To create a PivotChart:

1. Select any cell in your PivotTable. Clicking a cell in the PivotTable.
2. From the Insert tab, click the PivotChart command. Clicking the PivotChart command.
3. The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK. ...
4. The PivotChart will appear.

• Use VLOOKUP

The job of the vlookup is to look for a value (either numbers or text) in a column. Once it finds a match, the vlookup will return a value from any cell in the same row as the match.