Analysis the data with Pivot Table in Google Sheet

Pivot table is the most commonly used function to do analysis data in sheets.

You can create a pivot table easily in google sheet with the data menu pivot commend.  Refer the picture.

 

 

The google sheet will create the pivot table in a new sheet; just select the required field in the right column of the pivot table.

Very simple steps with an added feature of sorting the value in pivot table in the options.

 

I have created a data sheet with a sample pivot table for you to understand it and work on the sheet to get practice of pivot table function.

 

In the sample data; I have also used a date and text function to fine the month representing the transactions.  You can use the function to create a meaning full pivot and reports can be generated easily.   Please give your feedback after using the sample data in the following data.

 

https://docs.google.com/spreadsheets/d/1dD8XmC8FKvxL2UTKM6maWuwaivyDmP-f5D4fFdfciYs/edit?usp=sharing

FREEZE TOP ROW TO VIEW HEADINGS OF A TABLE IN GOOGLE SHEET

GOOGLE SHEET - FUNCTIONS, It is easy to use and collaborate with our team.

At time we shall store the data in Google sheet.  When we scroll the data, the headings should be visible to us this function.

In the view menu, select the freeze button to get options of selecting the freeze the one row / multiple.  You can also freeze the column to scroll to the right.

I have given a sample data and menu example for ready reference.

Please follow the link:

https://docs.google.com/spreadsheets/d/1Uy_9LcsqKLYbmkUvi8Uq8M_BN9HHrlUvBluqT1OgWNA/edit?usp=sharing

Google Sheet - VLOOKUP Function

Google sheet is a good tool to work with our team and store our data safe.

How do we use the vlookup function in Google sheet.

vlookup function:

The function is used to pull the relevant/corresponding data from master table.

i.e. If you feed the role number of a student; the name and other details of the student can be pulled from student master table.

i.e. if you feed the payment voucher number in a cell, the google sheet will pull the corresponding invoice, PO, amount etc. from the master payment table.

Function syntax:-  =vlookup(searchkey,Range,index,0)

searchkey- the value which will searched
Range- the master table, where the searchkey value will be searched
index- the column number to be displayed / returned by the function.

Important note;
The search key; column should be the first column of the range (table array)
The function returns the first instance value if there are multiple searchkey in the given range

Example:  Click the link to view the example.

https://docs.google.com/spreadsheets/d/1lCGpQGIVkegjG7ME1FArZ393q4qY4oQq4KqWOWQIJaY/edit?usp=sharing
  

Google sheet and Network days function

Google sheet is a good tool to work with your team; share the data and collaborate each other.

One of the function in google sheet is Networkdays function used to calculate the number of working days between two dates i.e. start date and end date.

If you have list of closed holidays; feed the dates and incorporate in the formula to reduce the holidays from the working days.

Sheet will calculate / count all working days between Monday to Friday and reducing the holidays given by us.

The function will help us to work for the project on a focus manner; since it is in google sheet all the team members can record the progress in the sheet to complete it in time.

Example:  Refer the google sheet to under the function with an example / sample data

 

https://docs.google.com/spreadsheets/d/1DCv8e25kIzQFwV_5_6yL24RueE-lkjpcGoa9GVr0nD8/edit?usp=sharing