MS Excel

 CELL REFERENCE

Q1. what is cell reference?

Ans 

A cell or range of cells on a worksheet or on a different worksheet inside the spreadsheet are referred to as a cell reference.

The most basic reference is only the name of the referred cell followed by an equal sign.

For example: (=c5) refers to the value within cell c5.

 type of cell reference

Relative cell reference

Absolute cell reference

Mixed cell reference

relative  cell reference:-

A cell reference is by default a relative reference, which denotes that the reference is relative to where the cell is located. You are actually referring to a cell that is two columns to the left (C minus A)—in the same row (2)—if, for instance, you refer to cell A2 from cell C2.

For Example :  A5, A2:A5

Absolute cell reference:-

When copying a formula from one cell to another in Excel, an absolute reference is a cell reference where the column and row coordinates remain constant. The coordinates are corrected by adding the dollar sign ($) before them.

For Example : $A$5

Mixed cell reference:-

An alternative to absolute and relative cell references in Excel is a mixed reference. In the mixed cell reference, we only make reference to the cell's column or row. Therefore, the mixed reference would be $A1 in cell A1, for instance, if we simply wanted to refer to the A column.

For Example  à $B2, B$4

PIVOT TABLE

Q 2. what is Pivot table?

Ans

A pivot table is an effective tool for computing, condensing, and analysing data. A pivot table is a statistical table that condenses the information from a larger table. This summary may contain sums, averages, or other facts that the pivot table intelligently combines together.

When data needs to be aggregated, summed up, or examined, pivot tables are frequently utilised.

How pivot tables work

There are four key parts used when users develop pivot tables:

Columns: Only the field's distinct values are presented across the top when a field is selected for the column area.

Rows: The first column is filled with data when a field is selected for the row area. All row labels are unique values, just as the columns, and duplication are eliminated.

Values—Each value is retained in a pivot table cell and used to present the information that has been distilled. The sum, average, lowest, and maximum values are the most typical values.

Filters: Filters use the full database to perform a calculation or limit.

 

Uses of a pivot table

Users can quickly and easily respond to business queries using a pivot table. The following are typical applications for pivot tables:

to compute totals or averages in professional contexts. Counting sales, for instance, by region or department.

to display sums as a proportion of the total. Taking the sales of a certain product in comparison to all sales, for instance.

to produce a list of distinctive values. displaying the states or nations that have ordered a product, for instance.

to summaries a complex report in a 2x2 table.

to determine a dataset's maximum and minimum values.

immediately requesting data from an OLAP server (online analytical processing).

DATA TAB

Q 1. what is data tab?

Ans

A document with columns, rows, and cells that hold certain values is known as a data table. They save data that users can access later and amend as necessary. The title, column headings, and row headers of the data table can aid a user in better understanding the data in the table. Additionally, the document performs a number of actions on the data, including searching, filtering, ordering, and arranging.

The majority of data tables include information about collections of data. An administrator of a kids' summer camp, for instance, may use a data table to keep track of the attendees' names, arrival times, registration status, and contact information.

This enables them to separate the most crucial information from their records and keep it organized according to importance. Keeping all of this data in one location can make it simpler to access and can also assist data analysts in drawing insightful conclusions about a table's contents.

To import data from Text file

Steps

1. Go to Data Tab :Get External Data -From Text

2. Select the text file that you want to import -import

3. Text Import wizard window will appear, select the data type that describes the data of file

There are two options:

i.                     Delimited:- Characters such as commas and tabs

ii.                   Fixed width:- Fields are aligned Choose any one of above option

4. If data has headers, then put tick (ü) in “My data has header” :-Next

5. Now, the second window will appear where you have to set the delimiters that have been used in text file, such as tabs, commas, spaces, etc.

6. In data preview box, you can see the separation of fields in columns. :-Next

7. Third window will appears, this screen lets you set the data format.

8. Select the format such as general, date, etc. :-Finish

9. After that, select the range where you want to put data. - OK

DATA TAB:

1. ADVANCE FILTER

2. SORT

3. TEXT TO COLUMN

4. FLASH FILL

5. GET EXTERNAL DATA (SHARING)

6. VALIDATION 

1. ADVANCE FILTER:-

You can create a distinct list of objects using Advanced Filter, then extract those items to another location in your worksheet or workbook. There are various helpful features in Advanced Filter. Unlike AutoFilter, it enables you to simultaneously apply multiple filter criteria to the whole data file.

2. SORT:-

A range or array's contents are sorted using the SORT function. In this example, we're using =SORT(A2:A17), replicated across cells F2, H2, and J2, to separately sort by Region, Sales Rep, and Product.

3. TEXT TO COLUMN:-

Using this option, you can divide a single text column into several columns. You have the option of splitting it into equal segments or segments at each comma, period, or other character.

    Steps:

1.           Select the cell that have the text

2.           Go to Data Tab à Text to Column

3.           Follow the Text Wizard Instructions

4.           Then the data will be separated in each column.

4. FLASH FILL:-

lash fill is a data tool in excel that will allow you to combine, extract

or transform data based on few examples.

Shortcut key à Ctrl + E

5. GET EXTERNAL DATA (SHARING):-

Data that is located somewhere other than the Excel workbook is

referred to as external data. The fact that Excel permits obtaining

external data from a wide range of sources means that the other

location might be practically anyplace. Examples include

information that is kept in text files, other programmer, or on

websites.

6. VALIDATION :-

Data validation is a feature that restricts (validates) user input to a worksheet. Technically, you can create a validation rule that controls what kind of data can be entered into a certain cell.

Data Validation can do following:

·    Allow only numeric or text values in a cell.

·    Allow only numbers within a specified range.

·    Allow data entries of a specific length.

·    Restrict dates and times outside a given range.

Restrict entries to a selection from a drop- down list.

Consolidate

Data consolidation refers to the collection and integration of data from multiple sources into a single destination. During the process, different data sources are put together or consolidated into the single data store.

Consolidation techniques allows organizations to more easily present, analysis data.

Steps: to consolidate sales data:

1. Create three worksheet with compatible data of sales for Jan, Feb and March.

2. Take another new sheet (fourth).

3. Point the position on fourth sheet

4. Data -Consolidate

5. Give source reference of each worksheet and click on add button to add all reference.

6. Comes on fourth sheet

7. Remove reference selection

8. Select first row, left column, and link sheets option

9. Click OK. 

what if analysis?

scenario manager

A scenario is a collection of values that Excel saves and can replace on your worksheet without your intervention. You can build and save various value groups as scenarios, then switch between them to examine various outcomes.

Once you have all the scenarios you require, you may compile data from all the sources to create a scenario summary report.

Steps

1. Create labels like Monthly incomes, List of expenses, Total Expenses, Savings

2. Go to Data Tab-What if analysis-Scenario Manager -Add

3. Provide Scenario Name and in changing cells, select the cells of changing values.

4. A box will appear, in this Add value for each cells

5. Ok-Show

6. Now, the value can be seen in cells of excel

7. Similarly, add different scenarios

8. After this, click on summary

9. Also select result cells

To provide name to the cell reference

1. Select the cell

2. Go to Formula Tab -Name Manager -Define Name-OK

Goal Seek

Goal Seek is a built in excel tool that allows you to see how one data item in a formula impacts another.

By using this, we can change the value of cell which contains formula to the desired value.

Steps

1. Select the cell which includes formula

2. Go to Data Tab -What if Analysis -Goal Seek

3. A dialog box will appear, in this box

Following inputs are required:

·   Set Cell -select the cell in which you want to change value To Value- The desired vales By changing cells -select the cell by which changing the value will affect the another cell.