Excel 2016 Data Tab lesson-7

Excel 2016 Data Tab lesson-7

Data:- We use Data tab for the large amount of data.It is useful to import the data by connecting with the server, and we can import data automatically from web, MS Access etc. ... Data connections are links to the data outside the workbook which can be updated if the source data changes.

From Access:-Excel 2016 can import data from an Access 2016 database table into a worksheet, a process known as making an external data query. ... In Excel, click the From Access button in the Get External Data group on the Data tab. Excel opens the Select Data Source dialog box

From Web:-Import data from a web page.

From Text:-Import data from a text, comma-separated value or formatted text (space delimited) file. 

From Other Sources :- On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog box is displayed.In the Choose Data Source dialog box, click the Queries tab.Double-click the saved query that you want to open.

Existing Connections :-Import data from common sources.

Refresh All - (Ctrl + Alt + F5). Button with Drop-Down. The button updates all the information in the active workbook coming in from external sources. The drop down includes the commands: Refresh, Refresh Status, Cancel Refresh and Connection Properties.

Connections:-In Excel, a connection can be created to directly link to a particular database filtered according to your requirements. This allows you to report SQL data, attach a table of data into Excel, create a pivot table and have better manipulation of your SQL data.

Properties:- This is only enabled when you select a cell that has been populated using an external connection.

Edit Links:-If you need to update or delete links that connect your spreadsheet and other files, the Edit Links command may ease your work. The Edit Links command enables you to view and edit all the other files your spreadsheet is linked to.

Sort:-The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results. The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array.

Filter:-Begin with a worksheet that identifies each column using a header row. ... Select the Data tab, then locate the Sort & Filter group.Click the Filter command. ... Drop-down arrows will appear in the header of each column.Click the drop-down arrow for the column you want to filter. ... The Filter menu appears.

Clear:-If you can't find specific data in a worksheet, it may be hidden by a filter. For example, if you have a column of dates in your worksheet, that column may have a filter that restricts the values to specific months. ... Clear a filter from a specific column. Clear all filters. Remove all filters.

Reapply:-When you reapply a filter or sort operation, different results appear for the following reasons: Data has been added to, modified, or deleted from the range of cells or table column. The filter is a dynamic date and time filter, such as Today, This Week, or Year to Dat

Advanced:- Advanced Filter allows you to filter using more than two criteria and also allows you to use formulas in your conditions.

Advanced Filter also allows you to obtain a list of unique items and/or copy the matching rows to another location.

The conditions which are used by the advanced filter have to be specified in separate cells to the actual table (typically above the table)

The specification of the conditions is very similar to that of the Database Functions.

Text To Columns:

-Excel's Text to Columns feature splits text in a cell into multiple columns. This simple task can save a user the heartache of manually separating the text in a cell into several columns

Remove Duplicates:-When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

Select the range of cells that has duplicate values you want to remove.

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

For example, in this worksheet, the January column has price information I want to keep.

 

Data Validation:-Once data validation is applied, you can ask Excel to circle previously entered invalid values. On the Data tab of the ribbon, click Data Validation and select "Circle Invalid Data"

For example, the screen below shows values circled that fail validation with this custom formula:

Find cells with data validation

To find cells with data validation applied, you can use the Go To > Special dialog. Type the keyboard shortcut Control + G, then click the Special button. When the Dialog appears, select "Data Validation":

Copy data validation from one cell to another:-To copy validation from one cell to other cells. Copy the cell(s) normally that contain the data validation you want, then use Paste Special + Validation. Once the dialog appears, type "n" to select validation, or click validation with the mouse.

Clear all data validation:-To clear all data validation from a range of cells, make the selection, then click the Data Validation button on the Data tab of the ribbon. Then click the "Clear All" button

                                                                                                                

Consolidate :- Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet. In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that you can update easily.

Now click on the What-If Analysis. Excel

has the following What-if analysis tools that can be used based on the data analysis needs

Scenario Manager

Goal Seek

Data Tables

Data Tables and Scenarios take sets of input values and project forward to determine possible results. Goal seek differs from Data Tables and Scenarios in that it takes a result and projects backward to determine possible input values that produce that result.

Scenario Manager

A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. Below are the following key features, such as:

You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

A scenario can have multiple variables, but it can accommodate only up to 32 values.

You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side.

Scenario Manager is a dialog box that allows you to save the values as a scenario and name the scenario.

Consolidate :- To start using the Data Consolidation tool, you need to select an empty sheet in the workbook as your master worksheet or add a new one if necessary. The worksheet is renamed ‘Consolidated Summary’.

Select the upper-left cell of the area where you want the consolidated data to appear.

On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog

In the Function box, click the summary function that you want Excel to use to consolidate the data. As you will see from the drop-down, there are 11 functions to choose from. For our data we want to add up the values so we’ll set the Function to Sum. 

Click in the Reference area and select the first data range to consolidate – to do this you will need to click the Sheet tab i.e. “Year 1” and then drag over the data (including row and column headings) and then click the Add button to add this first set of data to the consolidation dialog.

Step 1: Click the Data tab.

Step 2: Go to the What-If Analysis button and click on the Scenario Manager from the dropdown list.

Step 3: Now a scenario manager dialog box appears, click on the Add button to create a scenario.

Step 4: Create the scenario, name the scenario, enter the value for each changing input cell for that scenario, and then click the Ok button.

Step 5: Now, B3, B4, B5, B6, and B7 appear in the cells box.

Step 6: Now, change the value of B3to 500 and click the Add button.

Step 7: After clicking on the Add button, the add scenario dialog box appears again.

In the scenario name box, create scenario 2.

Select the prevent changes.

And click on the Ok

Step 8: Again appears scenario values box with the changed value of B3 cell.

Step 9: Change the value of B5 to 20000 and click the Ok button. 

Step 10: Similarly, create Scenario 3 and click the Ok button.

Step 11: Again, appears scenario values box with a changed value of the B5 cell.

Step 12: Change the value of B7 to 10000 and click the Ok button.The Scenario Manager Dialog box appears. 

In the box under Scenarios, You will find the names of all the scenarios that you have created.

Step 13: Now, click on the Summary button. The Scenario Summary dialog box appears.

Step 14: Select Scenario summary under Report type and click Ok. Scenario Summary report appears in a new worksheet. You will get the following Scenario summary report.

2. Goal Seek

Goal Seek is useful if you want to know the formula's result but unsure what input value the formula needs to get that result. For example, if you want to borrow a loan and know the loan amount, tenure of loan and the EMI that you can pay, you can use Goal Seek to find the interest rate at which you can avail of the loan.

Goal Seek can be used only with one variable input value. If you have more than one variable for input values, you can use the Solver add-in

Step 1: On the Data tab, go What-If Analysis and click on the Goal Seek option.

Step 2: The Goal Seek dialog box appears.

Step 3: Type C9 in the Set cell box. This box is the reference for the cell that contains the formula that you want to resolve.

Step 4: Type 57000 in the To value box. Here, you get the formula result.

Step 5: Type B9 in the By changing cell box. This box has the reference of the cell that contains the value you want to adjust.

Step 6: This cell that the formula must reference goal Seek changes in the cell that you specified in the Set cell box. Click Ok.

Step 7: Goal Seek box produces the following result.

A Data Table is a range of cells where you can change values in some of the cells and answer different answers to a problem. For example, you might want to know how much loan you can afford for a home by analyzing different loan amounts and interest rates. You can put these different values and the PMT function in a Data Table and get the desired result. 

A Data Table works only with one or two variables, but it can accept many different values for those variables.

Step 1: Create the required table.

Assume that the interest rate is 10%.

List all the required values.

Name the cells containing the values.

Set the calculation for EMI, Cumulative Interest and Cumulative Principal with the Excel functions PMT, CUMIPMT and CUMPRINC, respectively.

Below is the created table.

Step 2: Type the list of interest rate values that you want to substitute in the input cell.

As you observe, there is an empty row above the Interest Rate values. This row is for the formulas.

Step 3: Type the first function (PMT) in the cell one row above and one cell to the right of the column of values. Type the other functions (CUMIPMT and CUMPRINC) in the cells to the first function's right.

Step 4: The Data Table looks as given below.

Step 5: Select the range of cells that contains the formulas and values that you want to substitute, E2:H13.

Step 6: Go to the Data tab, select What-if Analysis and click on the Data Table tool in the dropdown list.

Step 7: Data Table dialog box appears.

Click in the Column input cell box.

And click on the Interest_Rate cell, which is C2.You can see that the Column input cell is taken as $C$2.

Step 8: Click on the Ok button.

Group/ Ungroup:- There are two visual signs of grouped worksheets in Excel:

The sheet tabs in a group have a white background; the sheet tabs outside the group appear in gray.

The word Group is added to the name of the workbook; as soon as the worksheets are ungrouped, it disappears.

After you've made the desired changes, you can ungroup the worksheets in this way:

Right-click any sheet tab in the group.

Choose Ungroup Sheets in the context menu.

Subtotal:- The next step would be to apply the SUBTOTAL function. This can be done as shown below:

Select the Data tab, and click on SUBTOTAL.

When we click on it, the Subtotal dialogue box will appear as shown below:

Now, click the drop-down arrow for the “At each change in: field.” We can now select the column we wish to subtotal. In our example, we’ll select Color.

Next, we need to click the drop-down arrow for the “Use function: field.” This will help us select the function we wish to use. There are 11 available functions. We need to choose depending on our requirements.  In our example, we’ll select SUM to find out the total number of T-shirts lying in each warehouse.

Next, we move to “Add subtotal to: field.” Here we need to select the column where we require the calculated subtotal to appear. In our example, we’ll select Number of Units in Warehouse I and Warehouse II.

After that, we need to click OK and we will get the following results:

As we can see in the screenshot above, the subtotals are inserted as new rows below each Group. When we create subtotals, our worksheet is divided into different levels. Depending on the information you wish to display in the worksheet, you can switch between these levels.

The level buttons in our example are images of buttons for Levels 1, 2, 3, which can be seen on the left side of the worksheet. Now suppose I just want to see the total T-shirts lying in the warehouse of different colors, we can click on Level 2.

Show Detail:-Can Show Detail

Hide Detail:- Can Hide Detail