SEC | S20W4 | Google Sheets features and functionality (Creating Forms & Sheets)

in spreadsheet-s20w4 •  3 months ago 

Hello everyone! I hope you will be good. Today I am here to participate in the contest of @simonnigwe about the Google Sheets features and functionality (Creating Forms & Sheets). It is really an interesting and knowledgeable contest. There is a lot to explore. If you want to join then:



Join Here: SEC | S20W4 | Google Sheets features and functionality (Creating Forms & Sheets)



Creating Forms & Sheets.png

Designed with Canva

Explain your understanding of Google Sheets and then describe in detail the various features of a Google Sheets, a pictorial representation of these features is necessary so ensure you have clear screenshots.

Google sheets is one of the famous products of the google. These are widely used in the whole world for different purposes. Google sheets is a cloud based spreadsheet software application. It was developed by Google. We can use them at anywhere and we can work collaboratively using Google sheets. Different people from the different corners of the world can work collectively and can have access at real time.

Google sheets allows the users to create, edit and collaborate on spreadsheets in real time. We do not need any additional software to install to use Google sheets like Excel. We just need a browser and internet to access Google sheets. Just like Excel, Google sheets offer powerful tools for the data manipulation, analysis and visualization.

As it is a cloud based service so we can access the Google sheets documents from any device. There is no any restrictions to use a specific device. We only have need of a google account to access Google Sheets. We can easily collaborate with others by sharing access and editing permission. Similarly we can give limited access to some users to the Google sheets.

I personally use Google Sheets fro the delivery of the content to the clients. And I find Google sheets very easy to use and operate.

Key Features of Google Sheets

Google sheets offer multiple features to the users. These features are given below:

Real-time Collaboration

  • Multiple users can work on a single sheet at the same time.
  • We can track the changes in real time as well as the input of individual users with different colour.
  • Comments and chat features are also available and they allow for the discussion and collaboration within the document.

image.png

This is how we can access this real time collaboration feature.

Formulas and Functions

  • Google Sheets offers different formulas such as mathematical, logical, statistical and text functions.
  • It offers different functions as well such as SUM, AVERAGE, IF, VLOOKUP, IMPORTRANGE, and ARRAYFORMULA.
image.pngimage.png
Manual Formulas EntryBuilt-in Formulas and Functions

We can use formulas by entering = and then we can see all the available formulas. You can see after = I entered SUM and it showed me all the formulas related to SUM.

Data Visualization

  • Google Sheets offer to create various types of charts. We can make bar, line, pie and scatter chart to visually represent the data.
  • We can edit the charts to customize their graphs with labels, colours and formats.

image.png

Conditional Formatting

  • Conditional feature allows users to automatically apply formatting such as colour coding to cells that meet specific criteria.
  • For example We can highlight the cells with values greater than a set threshold. We can also differentiate positive and negative numbers.

image.png

Data Validation

  • This feature restricts the type of data that can be entered in a cell or range. For example we can create drop down lists, restrict numeric entries or prevent duplicate values and entries.
  • It helps to maintain data consistency and accuracy.


image.png

Sometimes data validation is very important so it is how we can add rules for the validation of the data.

Pivot Tables

  • Google Sheets allows us to create pivot tables. We can use it for data summarization and analysis. Pivot tables enable users to organize and analyze large datasets. We can filter, sort and group information in the sheets.
image.pngimage.png
Steps to add Pivot TablePivot Table added sucessfuly

Sharing and Permissions

  • Google Sheets makes it easy to share documents with specific people or publicly.
  • We can control the level of access for each person with whom we share the sheet.
  • The document history feature allows users to see previous versions of the sheet and restore older versions if needed.

image.png

Sharing and permission is same as the real time collaboration. There are the same steps to follow to share sheets with others and to set permission and restriction to the users.


image.png

Further we can directly share our sheets at the websites as you can see in the above screenshot.

Integration with Other Google Apps

  • Google Sheets can be integrated with other Google services. We can integrate them with Google Drive, Google Forms and Google Data Studio.
  • It also supports the import and export of Excel files and other spreadsheet formats.

image.png

It is very simple to integrate google sheets with the other Google services such as forms. We can link Google forms to get the data from the forms to input in the sheet.

image.png

Here we can see that the Google form has been created in the sheet named Steemit. And heer we can input data in the forms and it will be added into the Sheet.

Add-ons

  • Google Sheets offer an Add-ons marketplace. The users can install third party tools for additional functionality.


image.png

It is how we can get add-ons in the Google Sheets to automate and facilitate our work.

Mobile Access

  • We can access Google Sheets via its mobile app. It is available on both Android and iOS platforms.


Create a simple Google form with the title Google Sheets 101 and use all the question types such as (Multiple choice, Short answer, Paragraph, Checkbox, Dropdown, etc) format to create your desired questions in the form. Customize all questions to be required and add a banner at the heading of your form. Clear screenshots are needed for this task.

Google forms are very useful which help us to collect data from the users in an organized way. It is very simple to create Google forms. These are the steps to create a Google form with the name Google Sheets 101:

image.png

  • First of all we need to choose the menu from the Google profile.
  • Then we can scroll down to choose Forms. It opens Google forms
  • We can also access google forms directly here

image.png

  • As we need to create a custom form so we should choose a Blank Form.
  • It will open a new window of the blank form.

image.png

  • The blank google form is here. Now we can customize it according to our needs.
image.pngimage.png
Adding Title and DescriptionCustomized Title and Description

image.png

In order to add the banner we can follow these steps.

  • First of all click on the `Customize Theme.
  • Then click on Choose Image.
  • Choose from the available images or upload your own image.
  • Then click Insert the image will be added.

image.png

Here you can see that the banner has been added to the form. And according to the banner background colour as pink has also been added. And it is looking beautiful so I am not going to change it.

image.png

In the above image you can see that I have added a multiple choice question*. It has a question section and an answers section. There are 3 options from which the user can choose one option which is suitable according to the question. **At the right lower side you can see that I have made it required to be answered and the toggle button became orange.

In this way we can add multiple choice questions in the Google Forms

image.png

Here I have added a short question to the Google form. It has a title and another section where the user will be able to input text as the short answer. I have made this question required.

In this way we can add short questions in the Google Forms

image.png

Paragraph questions look similar to the short questions. But these are the long questions and they required long answers. The input field allows the users to input long text. I have made the paragraph answer required to be answered.

In this way we can add paragraph questions in the Google Forms

image.png

Checkbox questions are also widely used and it is very easy to add checkbox questions in the Google forms. There the title of the question and then we can add different options for the check boxes from which the users can select the boxes. It is agian required question.

In this way we can add checkbox questions in the Google Forms

image.png

Dropdown questions are also of great usage and we can use them to select one thing of choice from the given things. It is very simple to add dropdown questions. We just need to change the type of the question to dropdown. And then we have to add some options for the dropdown list. And user will be able to select one option from the list. This question is also required for the user.

Google-Sheets-101-ezgif.com-crop.gif

Here is the complete output of the Google Sheets 101 google form. I have added a header and all types of questions in the form as mentioned by the professor. I have made all the questions required. The * with all the questions indicates that the question is required. Moreover when the user will continue without answering the question it will give a red alert to complete the answer.

Create a Google sheet titled SEC-S20W4 with two pages, the first page will be called Steemian Details and it should include the date of post, community posted, country, club, author, and Post link and the next page should be called Database. Your database should have author, country, and club so that when a post link is dropped in the Steemian details it can fetch the details automatically. (Ensure that your database has at least 30 Steemians and the same goes with the post link). Clear screenshots are needed for this task.

Google sheets really help us to store the data in an organized way. It offers cloud based service. It is same like the Excel and it has all the advance features to handle the data effectively.

image.png

Here I have created a Google Sheet with the name SEC-S20W4 and it has two pages the first page is of Steemian Details and second page is Database. Now I will add other required sections.


image.png

Here I have inserted all the data in the database. I have created all the 6 columns in the Stemian Details sheet which were mentioned by the professor. Then inserted data of 30 steemians in the Steemian Details sheet including the date, community, pakistan, username and club status. It took me a lot of time to enter all the data.

image.png

I have added a formula to extract the usernames from the posy link. This formula finds the text starting from @ and then it extract the text until / appears in the link. And we know that our username in the link starts with @ and it ends with slash /. After adding the formula when I am adding a link and then while dragging down the username section it is fetching username from the link and writing it in the Author column. So the formula added to fetch author at the top of the column is working fine.

As the country and the club status is not available in the link so we cannot extract it from the link we have to add it manually.


image.png

As per the requirements to fetch data from the Steemian details sheet to Database I have lined both the sheets with respect to their columns. I have linked author name from the first page to the second page and it is fetching data from the first sheet and inserting it to the second page where I have implemented the logic to get it.

image.pngimage.png

Similarly I have added linked the country as well as club columns to each other. After filling the data in the Steemian Details when the columns in the Database is dragged down it automatically fetches the data from the respective column and fill it in the respective rows.

While dragging down it actually drags down the formula and that formula populates the database with the details of steemians such as Author, Country and Club.

Now when I am adding a link in the steemain details while enhancing and dragging down the Author column it is fetching automatically the username. And after adding other details in the steemian details page when I am going to Database and dragging the columns further to the next row it is adding data in the rows by getting it from the first page.

image.png

Here you can see that the Database has been populated from the first page. It is how we can fetch any other details as well such date, community and link as well.

Using atleast pie chart & bar chart, show us the relationship between author and country as contained in the above question.

Now in order to show the data and the relationship between the country and author I have created a pivot table. It helps us to sow the desired data in an organized way and we can find relationship between the data as well using the pivot table as learnt in the previous class.

image.png

Here you can see to find the relationship between the country and the authors I have created a pivot table. I have added country in the rows. And in the value field I added the author as COUNTA to count the number of authors per country.

According to this table and the provided data the number of authors per country were calculated as follows:

CountryNumber of Authors
Bangladesh3
India1
Indonesia5
Nigeria4
Pakistan17
chart.pngchart (1).png
Pie ChartBar Chart
  • Pie Chart: The pie chart is showing the relationship between country and the number of authors in the percentage. The different colours of the pie chart represents different countries. According to the above chat majority of authors belong to Pakistan and minority of authors belong to India.

  • Bar Chart: The bar chart is also showing the same relationship between the author and the country. Each bar is showing the number of authors. In the x-axis bars are showing the number of authors. And y-axis is showing the country names.

In this way we can create more tables and find relationship between other things as well such as relationship between the author and club status or club status and country.

image.png

This chart shows the relationship between the authors and the club status. It shows how many authors are in which club status. So it is how we can find the relationship between any datasets using the pivot table ad then we can create a chart based on that relationship.

image.png

Here we can see the bar chart as well which is also showing the relationship between the number of authors and the club status. X-axis is showing the authors and the y-axis is showing the club status.

Finally the tasks have been completed. Indeed it took me a lot of time wile preparing it and presenting each thing in a step by step guide. I hope that it will help others as well to understand the concepts.

Google Sheet
Google Form

Here I have given access to the google sheet as an editor to the specified emails. And google forms is also available to view and fill.

Disclaimer: All the screenshots have been taken from Google Sheets and Google Forms otherwise stated.


I invite my friends @kouba01, @starrchis, @sergeyk and @fomabe to participate in this learning contest of Google Sheets and Google Forms.

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Upvoted! Thank you for supporting witness @jswit.

Loading...

Congratulations! - Your post has been upvoted through steemcurator06
1000152362.jpg

Curated by : @bonaventure24 - Selective Team

Thank you so much @bonaventure24

This straightforward, practical and easy-to-understand presentation is interesting and should be a reference for other users to deepen their knowledge on this topic.

Yes indeed it can be a guidance for others. Thank you for your appreciation and valuable feedback. It is highly appreciated.

I see Canva is your choice of image processing apps, is there any particular reason behind it? Does it have offline version?

It has a lot of features which make our work easy and smooth.

Okay. Enough reason to use it, no wonder it's one of the famous in it's category.

@mohammadfaisal.. How you give access of Google Sheet to just wanted persons

17283307037032504912058952964572.png

Follow these instructions to share it to others and manage the access.