What Is Scroll Bars In Google Sheets?
Scroll bars in Google Sheets are the default sliding vertical and horizontal bars on the right end and bottom of the Sheet window. They enable us to navigate through the spreadsheet in upward, downward, right and left directions. We can also create customized scroll bars that enhance the navigation capabilities through the Sheet.
Users can utilize the Scroll bars feature in Google Sheets to move quickly through the spreadsheet containing massive datasets. So, the feature enhances data accessibility and helps in effective data management.
For example, the following dataset shows the monthly sales figures of a firm.
The requirement is to display the sales value in the month mentioned in cell D2, with the month entered using a dropdown list in cell D2. Cell E2 shows the VLOOKUP(), which will return the required sales value once we update the month in cell D2.
Then, according to the definition of scroll bars in Google Sheets explained earlier, we can create a customized scroll bar to scroll up and down the dropdown list of months in cell D2.
In this insert scroll bars in Google Sheets example, we choose cell D2 and select the Google Sheets Data validation option, which is similar to Data Validation in Excel, under the Data tab in the ribbon. The Data validation rules pane appears on the right of the workspace.
The Apply to range field in the pane shows the chosen cell D2 address. Next, we set the Criteria field as the Dropdown (from a range) option from the dropdown list. After that, update the source range containing the list of months in the ensuing field, as shown in the image above. Click Done and close the pane.
Now, when we click the dropdown button in cell D2, we shall see the list of months, from which we must click the required month to choose it. In this case, since the list is long, we see a vertical scroll bar on the right of the list, which we can use to scroll through the list and select the required month.
Assume we must display the sales value for October month. Then, we can navigate downwards the list using the vertical scroll bar, and click on Oct to choose it and view the required sales value in cell E2.
Table of contents
Key Takeaways
- The scroll bars in Google Sheets are the vertical and horizontal sliding bars that appear by default on the right end and bottom of the spreadsheet window. They help us move up, down, left and right through the Sheet.
- We can create customized scroll bars using the Data validation, Freeze, and Table chart options.
- Users can make use of the scroll bars feature in Google Sheets to navigate spreadsheets containing massive datasets effortlessly and quickly. Thus, the feature aids in data analysis and management.
- We can optimize the scroll bars option in Google Sheets by reducing the dataset density, which we can achieve by removing unwanted complex formulas and formatting. On the flip side, we can split massive datasets into smaller chunks of data.
Types Of Scroll Bars In Google Sheets
There are vertical and horizontal scroll bars in Google Sheets.
#1 – Vertical Scroll Bar
The vertical scroll bar allows us to navigate up and down the rows in a spreadsheet.
#2 – Horizontal Scroll Bar
The horizontal scroll bar enables us to navigate left and right the columns in a spreadsheet.
How To Add Scroll Bars In Google Sheets?
We can insert scroll bars in Google Sheets in a customized way using the following method:
Adding Custom Scroll Bars With Data Validation
The steps to add custom scroll bars with the Data validation option are as follows:
- Choose the cell where we aim to have the scrollable list.
- Select Data – Data validation.
- The Data validation rules window appears on the right of the workspace, where we must click the Add rule button.
- The Data validation rules pane shows a few fields. While the Apply to range field will show the chosen cell’s address. Next, set the Criteria field as Dropdown or Dropdown (from a range). After that, specify the list of items or the address of the range containing the items in the fields below the Criteria field, according to the option set in the Criteria field.
- Click Done and close the Data validation rules window.
Now, the cell where we applied the Data validation rule will show a button with a dropdown arrow. We can click the dropdown arrow to view the list of items with a vertical scroll bar on the right of the list. Thus, we can now use the scroll bar to move up and down the list to select the required item.
Examples
Let us understand the effective ways of using scroll bars in Google Sheets with illustrations.
Example #1 – Add A Scrolling Table In Google Sheets (Custom Sized Table)
The source dataset shows the details of large-cap US companies.
The dataset is massive and difficult to navigate vertically and horizontally.
So, considering the meaning of scroll bars in Google Sheets explained earlier, we can create custom scroll bars using the Scrolling Table chart type.
Step 1: Select the source dataset range A1:L16 and choose Insert – Chart.
Step 2: The Chart editor pane opens.
We must choose the Table chart, under the Other category, as the Chart type. Next, the Data range field shows the chosen data range.
We can now view the customized Scrolling Table chart in the workspace.
We can now close the Chart editor pane and resize the customized table.
Next, we can now easily navigate the Scrolling table using the vertical and horizontal scroll bars in Google Sheets.
Example #2 – Scrolling Through Frozen Rows And Columns
The following dataset holds a list of employees and their details.
We must scroll through the data, with the column headings, the employee names, and the ID data being static.
We can achieve the requirement by using scroll bars in Google Sheets, which are customized using the Freeze option.
Step 1: Since we must freeze the first two rows and columns, choose the merged cells B1:B2 and select View – Freeze – 2 rows.
We can see rows 1 and 2 frozen.
Next, with the cells B1:B2 selected, choose View – Freeze – 2 columns.
We can see the first two columns frozen.
So now, we can see the vertical and horizontal scroll bars to navigate through the remaining unfrozen area in the workspace, with the first two rows and columns visible in each case.
Importance Of Scroll Bars In Google Sheets
The importance of the scroll bars feature in Google Sheets is as follows:
- Scroll bars enable us to navigate through spreadsheets holding massive datasets easily. Rather than scrolling one row or column at a time, we can effortlessly reach the required section of the dataset.
- Since we can create customized scroll bars, they help in improving data accessibility and management. Thus, the feature is useful when analyzing large and complex datasets.
Important Things To Note
- Assume we are unable to view the scroll bars in Google Sheets. Then, we should check the browser’s preferences or the settings menu to ensure the option to show the scroll bars is set correctly. Otherwise, we can adjust the zoom level using the browser’s zoom settings to ensure we are not in full-screen mode. The reason is that it is usually the reason for not being able to view the scroll bars.
- We cannot add a horizontal scroll bar to an individual row in Google Sheets as the application does not support it.
- We cannot customize the scroll bars’ appearance in Google Sheets since the browser controls it.
Frequently Asked Questions (FAQs)
You can get the top row to scroll in Google Sheets using the following steps:
1. Select a cell in the top row of the given dataset.
2. Choose View – Freeze – 1 row or View – Freeze – Up to row 1.
We shall see a thick border line below row 1, indicating that it is frozen. Further, we can see the vertical scroll bar at the right end of the window starting from the thick border, implying that we can scroll up and down the rows below the top row.
For example, the source dataset shows the date-wise order data of a store.
The aim is to get the top row to scroll, which we can do as explained below:
Step 1: Select a cell in row 1, say, cell A1. Next, choose View – Freeze – Up to row 1.
A thick border appears below row 1, with the vertical scroll bar starting from the thick border.
Now, we can slide the scroll bar up and down the rows below the top row while the top row remains fixed and constantly visible.
We cannot add a horizontal scroll bar to one row in Google Sheets since the application does not support it. It adds the scroll bars to the entire sheet by default.
You cannot customize the scroll bars’ appearance in Google Sheets within the application since the browser controls the scroll bars appearance.
Download Template
This article must be helpful to understand the Scroll Bars In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Scroll Bars In Google Sheets. We learn the types, importance, & how to add scroll bars in Google Sheets with examples. You can learn more from the following articles –
Leave a Reply