Google Sheets Basic Course In Hindi Language

Google Sheets offers a wide range of formatting tools as shown in the below image. Using these tools you can format your data as per your requirement like percentage format, decimals adjustment, font related formatting like font style, font size, font color, cell formatting like cell color, border line, merging of cells, horizontal as well as vertical alignments of text, wrap text, text rotation, etc.

If the heading of a table is not frozen, when users scroll the rows/columns, the headings will also get scrolled out of the screen along with the scrolled data, by using the freezing tool, users can easily freeze the row/column having headings. Click on the View menu and then click on "Freeze" as shown in video.

Sorting of data in Google sheets is just a one click task. Thousands of records can be sorted in ascending or descending order within seconds in Google sheets by following steps  mentioned in video.

• With the help of alternate colours tool users can change the colour of each alternate row. Various default colour combinations are given in this tool, however, users can also create a new colour combination of their choice. Follow the steps of video to apply alternate colouring to a range.

• Select the range and click on Format menu and then click on "Alternating colours".

• Collaborating with a team on Google Sheets is very simple and easy. Using this feature multiple users can work on same Spreadsheet. You can share a Google Sheet with anyone with 3 different rights. 1. Edit rights 2. View only rights 3. Only comment rights.

• In case of sharing with edit rights, users can edit, alter and delete the data and can download the Spreadsheet.

• With view only rights users can only view the data and in case of comment only rights, users can view and give comments on data but can not edit. View only or only comment rights can be assigned with/without downloading, printing and copy rights.

• In addition to above, rights to further share the Spreadsheet can also be restricted by the spreadsheet owner. If the sharing rights are restricted then editors, viewers and comments cannot further share a Spreadsheet Sheet to anyone without the permission of the Spreadsheet owner. Follow the steps of video to share a Google Sheet with multiple users.

Sharing the Google Sheet with specific users is already discussed in preceding lesson. In addition to earlier discussed sharing options, Google Sheets also offers the option of publicly sharing a spreadsheet i.e., "Any one with the link can access the sheet". Follow the steps given in video to share the sheet to any one with link.

• With the help of Protect sheets/ranges tool, one can restrict other users from editing or deleting the protected data. Protection can be applied to the entire worksheet or specific range of worksheet. User wise protection can also be applied.

• Click on the Data menu and then click on "Protect sheets and ranges" as shown in video.

Under the Print option, users can adjust the print settings like Headers & Footers, Formatting, Margins, Paper size etc.

With the help of downloads option, users can download the Google Sheet in multiple formats like Microsoft Excel, PDF etc.

With the help of email option, users can email the Google Sheet in multiple formats like Microsoft Excel, PDF etc.

At times there may be duplicate records within your data, for any analysis or other task you may require unique records of your data. Using the Remove Duplicate tool users can remove duplicate records and get the unique values in just a click. Select the range and then click on the Data menu and then click on "Data clean-up" and then select "Remove duplicates" as shown in video.

Google Sheets has a built-in tool “Split text to columns”. This tool gives the option to the user to split the content of one cell into multiple cells separated by any special character. It can also automatically detect the separation criteria if texts are in fixed or uniform format. Select the range and then click on the Data menu and then click on "Split text to columns" as shown in below video.

Google Sheets offer a very powerful feature of cell edit history. Using this feature you can check the complete edit history of any particular cell like what is edited, who has edited and when it was edited. To check the edit history of a cell right click on that cell and follow steps as shown in video.

Using hyperlink user can insert any link in a cell. The link can be used to navigate users from one range to another range within a worksheet, from one worksheet to another worksheet within a spreadsheet and from one spreadsheet to another spreadsheet. Hyperlink can also be used to open any external website, web address or any other cloud based file. To insert a hyperlink, right click on the cell and then click on "Insert link" as shown in video.

• Data filter tool allows the user to filter the data based on any common criteria. In the case where multiple users are working on a same worksheet, if one user applies a filter on the data then other users will get disturbed because their screen will reflect the filtered data.

• To overcome above issue, Google Sheets offers the option of “Create New Filter View”, by using this option, if a user creates a filter view to filter the data, other users won’t get disturbed, because user specific filter view will be created i.e., other users screen will not reflect the filtered data.

• Each user can create one or more personal filter views with his own filter rules without disturbing other user's view. Each filter view can be named and can be saved for quick access and can also be accessed by other users too.

• In the case of view only spreadsheets in which users do not have rights to edit the data, one can create a temporary filter view and filter the data.

• In the video, first we have discussed direct filter and then user specific filter is discuss.

Keyboard Shortcuts

Follow the steps of video to upload, open and convert the Microsoft Excel file to Google Sheets.

With the help of the Data Validation tool, user can define the data input criteria of the cell (like number, text, date, etc.). Any data outside the criteria will not be accepted in the cell. E.g. If numeric validation is applied to any cell, input other than number will not be accepted in the cell. Further, with the help of Data Validation tool you can insert a dropdown list in the cell and restrict the users to enter the data from that dropdown list only. This tool can also be used to convert a cell into a checkbox. All the above options have been explained with examples in video.

• Conditional formatting is a useful technique to format or highlight the cells if they meet the conditions applied. E.g. Highlight the entire row if column C is below 100.

• Conditional Formatting tool is available with a variety of options, there are some ready to use rules like cell value greater than, less than, etc. You can directly apply these rules. In addition to ready to use rules, you can design your customized rules by using the “Custom Formula” option.

• Multiple formatting options are available like highlight the cell, strike-through, bold text, text color, etc. To apply conditional formatting, select the range, click on the Format menu and then click on "Conditional formatting" and then follow the steps of video.

• In Google Sheets, images can be inserted from a variety of sources like drive of PC, from Camera, by pasting URL of the image, from Google Photos, from Google Drive and also from Google image search. Images can be inserted in two ways i.e. (1) image in the cell and (2) image over the cell.

• Images inserted in the cell will be added in the selected cell and the size of the image will automatically be adjusted according to the size of the cell. These images will be considered as a content of the cell itself like any text.

• In case of images inserted over the cell, they are not linked to any cell and will be added over the cells i.e. they will not be treated as a part of the cell but will be independent. Follow below steps to add image in any worksheet. Click on the Insert menu and then click on "Image" as shown in below image and follow the steps of video.

At times you may need to leave a note on any specific cell which may be used for reference in future or may be used as a message for other users. This can be done with the help of the comment option. Right click the cell in which you want to insert a comment and follow the steps of video.

To move the Google Sheet from one location to another click on the File menu and then click on "Move" as shown in below image and follow the steps of video.

With this feature, users can make their content available to anyone by publishing it to the web. Click on the File menu and then click on "Share" and then select "Publish to web" as shown in below image and follow the given steps.

• Google Sheets offer a very powerful feature of version history.

• Using this feature you can 1. Track the changes made by different users along with date and time of the change 2. Restore the sheet to earlier versions 3. Make a copy of any earlier version.

• Click on the File menu, then click on "Version history" and then click on "See version history" as shown in below image and follow the given steps.

• As the name suggests, with the help of Find, the user can find any word, value or any type of data within the spreadsheet, worksheet and any specified range.

• Using Replace users can find and then replace the word, value or any type of data. Click on the Edit menu and then click on "Find and replace" as shown in below image and follow the given steps.

The User can change a spreadsheet’s locale, time zone, display language and calculation settings. Setting the location will change the time zone and date/currency related functions. Click on the File menu and then click on "Settings" as shown in below image and follow the given steps.

• At times you may require to add prefix or suffix to numeric data like Kgs. 1,000 or 1,000 Kgs.

• E.g: (1) Without prefix:- 1,000 With prefix:- Rs. 1,000
          (2) Without suffix:- 1,000 With suffix:- 1,000 Kgs.

• Google sheet offers you to add your desired prefix or suffix by using the custom number format feature.

• Select the column in which you want to add the prefix or suffix and perform the steps shown in video.

Google Sheet offers a very powerful and simple to use tool to design charts and graphs from Spreadsheet data. Using this tool one can design multiple types of charts like Pie Chart, Column Chart, Bar Chart, Line Chart, Scatter, Map etc. Select the range and then click on the Insert menu and then click on "Chart" as shown in video and follow the given steps.

• After getting a basic understanding of Google Sheets let’s understand a few basic functions offered by Google Sheet. With the help of functions, users can design different formulas which can be used to perform different types of calculation on the data, to modify/combine/analyze the data and can even be used to import the data from one Google Sheet to another.

• There are numerous functions available in Google Sheets but we shall start with few basic functions.

• To design functions in a cell users have to start with = and then to write the function.
  E.g. =SUM for SUM function. Check video for details.

Average function is used to get the average of numerical data.

Search within formula : At times you may have entered formula in multiple cells or multiple worksheets, using the option of "Search within formula" you can find and replace the formula in specific range, entire worksheet or all worksheets in one go. Click on the Edit menu and then click on "Find and replace" and check the video for details.

• Using the translate function you can convert the text from one language to another.

• Many languages are supported by this function.

• Each language has a code like “en” is for english and “hi” is for hindi.

• Formula Example : GOOGLETRANSLATE(A2,"en","hi")

• Formula Explanation: The above formula will convert the text in cell No. A2 from english to hindi.

Using TODAY function you can get the current date. Just enter =TODAY() in any cell and you will get the current date.

• Using TRANSPOSE function you can convert the row data into column or column data in row.

• Formula Example - column to row: =TRANSPOSE(A2:A5)

• Formula Explanation: The above formula will convert the data of A2 to A5 in one row i.e. the vertical data will be converted into horizontal.

• Formula Example - row to column: =TRANSPOSE(A2:D2)

• Formula Explanation: The above formula will convert the data of A2 to D2 in one column i.e. the horizontal data will be converted into vertical.

• LEFT function gives specific characters starting from left & ending at specified length.

• Formula Example: =LEFT(A2,4)

• Formula Explanation: The formula will extract the first 4 characters of the cell.

• Formula Example: =RIGHT(A2,4)

• Formula Explanation: The formula will extract the last 4 characters of the cell.

• Using "&" you can merge text of different cell

• Formula Example: =A2&B2

• Formula Explanation: The formula will merge text of cell A2 & B2.

• Upper function converts lowercase text to uppercase.

  • Formula Example: =UPPER(A3)

  • Formula Explanation: The formula will convert the entire text of cell A3 in upper case.

• Lower converts uppercase text to lowercase.

  • Formula Example: =LOWER(A3)

  • Formula Explanation: The formula will convert the entire text of cell A3 in lower case.

• SORT function is used to sort the data in ascending/descending order.

• Formula Example (single column data): =SORT(A3:A,1,TRUE)

• Formula Explanation: 1 is the column number i.e. A and TRUE stands for ascending order i.e. sort by column 1 ascending. Replace TRUE by FALSE for descending order.

• COUNT function is used to count the cells containing numbers.

• COUNTA function is used to count the cells containing any type of data.

UNIQUE function is used to get the unique value of a range.

• ROUND:- ROUND Function helps in rounding the value in accordance.
  For E.g.: (i) If the value is 100.49, it will result in 100 (ii) If the value is 101.50, it will result in 102.

• ROUNDUP:-Roundups the value to the next full digit.
  For E.g. if the value is 100.49 or 100.50, then it will result in 101.

• ROUNDDOWN:-Round downs the value to previous full digit.
  For E.g. if the value is 100.49 or 100.50, then it will result in 100.

• IF function is used to reflect results based on condition i.e if a condition is satisfied, formula will return a particular value and if condition is not satisfied then formula will return another value.

• IF function has three parts: logical_expression i.e. condition, value_if_true i.e. result if condition is satisfied, value_if_false i.e. result if condition is not satisfied.

• Formula Example: =IF(A2="City","Yes","No")
  
  " " inverted commas are used to give reference to text, reference to number is to be given without inverted commas E.g. A2=1.

• Formula Explanation: If Cell A2 = “City” then formula result should be Yes otherwise No.

• Detailed discussion on IF function is covered in advance course.

• SUMIF function is used to get the sum of range on the basis of some common criteria.

• E.g. Column B has items and column C has order quantity. Using SUMIF you can get the total order quantity of any item.

• SUMIF function has three parts: range, criterion i.e. criteria, sum range.

• Formula Example: SUMIF(A2:A10,"Item 1","B2:B10")

• Formula Explanation: Get the total of column B for Item 1 in column A.

• VLOOKUP can be used to search for a matching value in a column and return the corresponding value from a different column.

• VLOOKUP function has three parts viz. Search Key i.e. the value to be searched, Range i.e. the columns to be searched and Index i.e. the column number of the range to be returned.

• Formula example: =VLOOKUP(D3,A3:B11,2,FALSE)

• FALSE or 0 will find the exact match and TRUE or 1 will find the nearest match, FALSE should be used for accurate match.

• When FALSE is used and if there are multiple matches in the Range then the first match will be returned.

Array Formula : Applying a formula in multiple cells is time consuming and tedious task, but using ARRAYFORMULA you can apply a formula once in a cell and it will be automatically applied to other cells. Check the below image for details.