Microsoft Excel 2016 Tutorial for Beginners Part 1 Full Intro Learn How to Use Excel 2016

From Subjectmoney 

Excel 2016 Tutorial for beginners

This is a Microsoft Excel 2016 Full Tutorial for beginners & intermediate users.In this crash course Excel video tutorial you will learn how to use Excel 2016 formulas and functions, Excel 2016 new features, Excel 2016 Charts along with many other necessary features that you must know how to use when using Excel 2016.

1:38 The Excel Ribbon

2:10 Ribbon Groups and Commands in Excel 2016

2:30 Data Entry in Microsoft Excel

3:08 How to enter data in to a cell in Excel

3:22 How to enter data into the formula bar in Excel

3:50 How to Navigate through Microsoft Excel

4:24 How to navigate and select cells in Excel with keyboard

8:01 Autofill in Excel 2016

9:15 Autocomplete in Excel 2016

10:32 How to zoom in and zoom out Excel 2016

10:50 Formulas & Functions Tutorial in Microsoft Excel 2016

11:25 Addition in Excel

13:04 Autosum Function in Excel

14:03 The Sum Function in Excel 2016

15:45 Subtraction in Microsoft Excel

16:12 Multiplication in Excel 2016

17:15 The Product Funtion/Formula in Microsoft Excel

17:44 How to Find/Search for a function in Excel using the insert function command in the Function Lirbrary

19:29 Division in Microsoft Excel

19:46 the Quotient Function in Excel

21:25 Exponential formulas in Excel

22:20 Concatenation in Excel

23:06 Logical Comparison in Excel

23:08 Equal to logical comparison in Excel

24:17 Logical Comparison Greater Than in Excel

24:36 Less Than Logical Comparison

24:58 Greater than or equal to

25:42 Not Equal to Excel 2016

26:14 Combining Formulas and Functions in Microsoft Excel 2016

29:18 Cell Reference Tutorial in Microsoft Excel

29:57 Types of cell reference in Excel

30:07 Relative Cell Reference in Excel 2016

32:04 Using Autofill for formulas and cell reference

32:25 Show formula in Excel 2016

33:15 Absolute cell reference in Excel 2016

33:10 How to lock cell reference

34:40 Mixed Cell Reference locking only the row or column

39:27 How to refer to cells in another worksheet in Excel

40:06 How to refer to another workbook in Excel

40:35 How to clear/remove/delet content in a cell

40:52 Autofill dates in Excel

41:15 How to delete a row or column in Excel

46:36 How to style and Excel worksheet

46:50 How to format numbers in Excel

47:50 increase of decrease decimals in Excel

48:54 How to format dates in Excel (short date, long date)

40:32 How to resize cells columns and rows in Excel

50:35 the Today function in Excel Today()

51:54 Formattin and styling cells in Excel

52:05 How to add color fill in Excel

52:55 How to add borders in Excel

53:44 How to copy and paste a range of cells in Excel

54:23 Cell Styles in Excel 2016

55:27 Text direction and how to angle diagnal text content and numbers in Excel

57:00 How to Merge cells in Excel

57:53 Merge across in Excel

58:18 Change height or width of cell in Excel

58:49 Font Style in Excel

1:01:37 content alignment in Excel 

1:02:20 Conditional Formatting in Excel 2016

How to Split Cell Diagonally

How to Split Cell Diagonally

How to Split One Cell Row into Multiple Rows

Below are some videos on how to one cell row into multiple rows

What-If Analysis - Data Tables

From excel-easy.com

Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.

One Variable Data Table

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).

2. Type the different percentages in column A.

3. Select the range A12:B17.

We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.

4. On the Data tab, click What-If Analysis and select Data Table from the list.

5. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.

Note: this is a one variable data table so we leave the Row input cell blank.

6. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and press Delete.

Two Variable Data Table

To create a two variable data table, execute the following steps.

1. Select cell A12 and type =D10 (refer to the total profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

4. Select the range A12:D17.

We are going to calculate the total profit for the different combinations of 'unit profit (highest price)' and '% sold for the highest price'.

5. On the Data tab, click What-If Analysis and select Data Table from the list.

6. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.

7. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.

8. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.

What-If Analysis - Goal Seek

From excel-easy.com

What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the answer.

1. On the Data tab, click What-If Analysis, Goal Seek.

The Goal Seek dialog box appears.

2. Select cell D10.

3. Click in the 'To value' box and type 4700.

4. Click in the 'By changing cell' box and select cell C4.

5. Click OK.

Result. You need to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.