Thanh Tran - Excel data analysis by examples: Excel data analysis for complete beginners, Step-By-Step Illustrated Guide to Mastering Excel data analysis (Excel advance Book 1)

EXCEL DATA ANALYSIS BY EXAMPLES Excel data analysis for complete beginners, Step-By-Step Illustrated Guide to Mastering Excel data analysis Excel is the worlds leading spreadsheet application. Its a key module in Microsoft Officethe number-one productivity suiteand 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

Sort by Color, Reverse List, Randomize List 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. 4. 4.

Click 'Copy Level' two times and select the other colors. 5. Click OK. Result.

This article teaches you how to reverse a list in Excel. 1. 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.

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.

Number and Text Filters, Date Filters, Advanced Filter, Data Form, Remove Duplicates, Outlining Data, Subtotal. 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. 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.

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: 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. 5. 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). 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.

Next page