Seite 2
EXCEL DATA ANALYSIS BY EXAMPLES EXCEL DATA ANALYSIS FOR COMPLETE BEGINNERS, STEP-BY-STEP ILLUSTRATED GUIDE TO MASTERING EXCEL DATA ANALYSIS Excel is the world’s leading spreadsheet application. It’s a key module in Microsoft Office—the number-one productivity suite—and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to- understand format. THANH TRAN Copyright © 2019 by Thanh Tran
Seite 3
TABLE OF CONTENTS Chapter 1. Sort Chapter 2. Filter Chapter 3. Conditonal Formatting Chapter 4. Charts Chapter 5. Pivot Tables Chapter 6. Tables Chapter 7. What-If Analysis Chapter 8. Solver Chapter 9. Analysis ToolPak
Seite 5
SORT BY COLOR This example teaches you how to sort data by color in Excel. 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Sort. The Sort dialog box appears. 3. Sort by Last Name (or any other column), sort on Cell Color (you can also sort on Font Color and Cell Icon), and select the green color for the first level. 4. Click 'Copy Level' two times and select the other colors. 5. Click OK.
Seite 7
REVERSE LIST This article teaches you how to reverse a list in Excel. For example, we want to reverse the list in column A below. 1. Enter the value 1 into cell B1 and the value 2 into cell B2. 2. Select the range B1:B2, click the lower right corner of this range, and drag it down to cell B8. 3. Click any number in the list in column B. 4. To sort in descending order, on the Data tab, in the Sort & Filter group, click ZA. Result. Not only the list in column B, but also the list in column A has been reversed.
Seite 8
RANDOMIZE LIST This article teaches you how to randomize (shuffle) a list in Excel. For example, we want to randomize the list in column A below. 1. Select cell B1 and insert the RAND() function. 2. Click on the lower right corner of cell B1 and drag it down to cell B8. 3. Click any number in the list in column B. 4. To sort in descending order, on the Data tab, in the Sort & Filter group, click ZA. Result. A random list in column A (sorted on the random numbers above). Note: random numbers change every time a cell on the sheet is calculated. If you don't want this, simply copy the random numbers and paste them as values.
Seite 9
CHAPTER 2. FILTER Number and Text Filters, Date Filters, Advanced Filter, Data Form, Remove Duplicates, Outlining Data, Subtotal.
Seite 10
NUMBER AND TEXT FILTERS To view all conditional formatting rules in a workbook, use the Conditional Formatting Rules Manager. You can also use this screen to create, edit and delete rules. 1. Select cell A1. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Manage Rules. The Conditional Formatting Rules Manager appears.
Seite 11
Note: because we selected cell A1, Excel shows the rule applied to the range A1:A10. 4. From the drop-down list, change Current Selection to This Worksheet, to view all conditional formatting rules in this worksheet. Note: click New Rule, Edit Rule and Delete Rule to create, edit and delete rules.
Seite 12
DATE FILTERS Data bars in Excel make it very easy to visualize values in a range of cells. A longer bar represents a higher value. To add data bars, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Data Bars and click a subtype. Result:
Seite 13
Explanation: by default, the cell that holds the minimum value (0 if there are no negative values) has no data bar and the cell that holds the maximum value (95) has a data bar that fills the entire cell. All other cells are filled proportionally. 4. Change the values. Result. Excel updates the data bars automatically. Read on to further customize these data bars. 5. Select the range A1:A10. 6. On the Home tab, in the Styles group, click Conditional Formatting, Manage Rules. 7. Click Edit rule. Excel launches the Edit Formatting Rule dialog box. Here you can further customize your data bars (Show Bar Only, Minimum and Maximum, Bar Appearance, Negative Value and Axis, Bar Direction, etc).
Seite 14
Note: to directly launch this dialog box for new rules, at step 3, click More Rules. 8. Select Number from the Minimum drop-down list and enter the value 100. Select Number from the Maximum drop-down list and enter the value 150. 9. Click OK twice.
Seite 15
Result. Explanation: the cell that holds the value 100 (if any) has no data bar and the cell that holds the value 150 (if any) has a data bar that fills the entire cell. All other cells are filled proportionally.
Seite 16
ADVANCED FILTER Color Scales in Excel make it very easy to visualize values in a range of cells. The shade of the color represents the value in the cell. To add a color scale, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Color Scales and click a subtype. Result: Explanation: by default, for 3-Color scales, Excel calculates the 50th percentile (also known as median, middle value or midpoint). The cell that holds the minimum value (9) is colored red. The cell that holds the median (36) is colored yellow, and the cell that holds the maximum value (80) is colored green. All other cells are colored proportionally. Read on to further customize this color scale.
Seite 17
4. Select the range A1:A7. 5. On the Home tab, in the Styles group, click Conditional Formatting, Manage Rules. 6. Click Edit rule. Excel launches the Edit Formatting Rule dialog box. Here you can further customize your color scale (Format Style, Minimum, Midpoint and Maximum, Color, etc). Note: to directly launch this dialog box for new rules, at step 3, click More Rules. 7. Select 2-Color Scale from the Format Style drop-down list and select white and blue. 8. Click OK twice. Result.
Seite 19
DATA FORM Icon Sets in Excel make it very easy to visualize values in a range of cells. Each icon represents a range of values. To add an icon set, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Icon Sets and click a subtype. Result:
Seite 20
Explanation: by default, for 3 icons, Excel calculates the 67th percent and 33th percent. 67th percent = min + 0.67 * (max-min) = 2 + 0.67 * (95-2) = 64.31. 33th percent = min + 0.33 * (max-min) = 2 + 0.33 * (95-2) = 32.69. A green arrow will show for values equal to or greater than 64.31. A yellow arrow will show for values less than 64.31 and equal to or greater than 32.69. A red arrow will show for values less than 32.69. 4. Change the values. Result. Excel updates the icon set automatically. Read on to further customize this icon set. 5. Select the range A1:A10. 6. On the Home tab, in the Styles group, click Conditional Formatting, Manage Rules. 7. Click Edit rule. Excel launches the Edit Formatting Rule dialog box. Here you can further customize your icon set (Icon Style, Reverse Icon Order, Show Icon Only, Icon, Value, Type, etc).
Die ersten 20 Seiten werden angezeigt. Laden Sie das Dokument herunter, um alles zu sehen.
Kommentare (0)
Noch keine Kommentare. Seien Sie der Erste!