SheetsIQ Addon General Tools

Auto Formula

Using this tool, you can apply formulas to a range automatically. 

To use this tool, from the homepage of the add-on, click on “Auto formula by range” under the General section. Once you have the “Auto formula by range” page is open, follow these steps:

Steps:-

1. Select a range in your sheet

2. Choose a formula from the drop down selection field which you want to apply

3. Choose whether you want the formula to be applied in rows or columns

4. Click on apply to see the result in your sheet

Function by color

Using this tool, you can apply google sheets functions by cell colors (both text and background colors).

To use this tool, from the homepage of the add-on, click on “Function by Color” under the General section. Once you have the “Function by Color” page is open, follow these steps:

Steps:

1. Select Range – 

When the “Function by color” page loads, it will try to pick up the current data range from your active sheet. This range will be shown in the range field. 

You can change this range in two ways.

Either you can select a range in your sheet and click on the “Get Range” button or you can type the range manually. Preferred way is the first one.

2. Select cell text color –

If you want to calculate based on cell text color, then you have to setup this option. 

First, check the box before “Cell text” to activate this option.

Next, select a cell whose color you want to use for calculation and then click on “Get color”.

In this example, we want to know the count and sum of red color cells. So we will select cell B2 and click on the “Get color” button.

3. Select cell background color – 

Similar to the above step, if you want to consider cells with background colors, you can follow the same steps to choose the cell background color.

First, select the checkbox to activate the option. Then choose a cell for which you want to consider the background color. Click on “Get color” to select that cell’s background color.

In this example, we do not have any background colors to consider, so we will not select the checkbox.

Pro Tip:- If you want to consider cells with cell text color say “red” and background color say “yellow”, then you have to select both checkboxes. 

4. Choose the function to apply –

Now you need to choose the function from the drop-down list to apply to the range.

5. Select result placement cell –

Here you need to select the cell where the result will be placed. 

By default an empty cell will be selected.

You can select a cell in your sheet and click on change to select that cell or type the cell value.

6. Apply – 

Now click on the “Apply” button to get the result in the result placement cell.

Change case

Using this tool, you can change the cell text case.

To use this tool, from the homepage of the add-on, click on “Change Case” under the General section. Once you have the “Change Case” page is open, follow these steps:

Steps:

1. Select Range – 

First you need to select a range of cells where you want to alter the case

2. Choose change case type – 

Now you can select one of the change case types from the available 6 options. Then click “Apply” to see the result.

Freeze & Unfreeze Panes

Using this tool, you can freeze or unfreeze data in one click.

To use this tool, from the homepage of the add-on, click on “Freeze & Unfreeze pane” under the General section. Once you have the “Freeze & Unfreeze pane” page is open, follow these steps:

Steps:

1. Freeze- 

First you need to select a cell such that this is the cell till which the rows and columns will be freezed. 

In the below example, I want to freeze the top row only. So I will select cell A2. 

Then click “Apply” to see the result.

To freeze both the first row and first column, you have to select cell B2. 

2. Unfreeze-

To unfreeze in a sheet where already some rows/columns has been freezed, just select the unfreeze option and click “Apply”.

Remove blank rows and columns

Using this tool, you can remove empty and unused rows or columns in one click.

To use this tool, from the homepage of the add-on, click on “Remove blank rows and columns” under the General section. Once you have the “Remove blank rows and columns” page is open, follow these steps:

Steps:

You do not need to select any range. The tool will automatically work on the current sheet.

Just choose the option and click on “Apply” to see the result.

Remove all empty rows –  This will remove all empty rows from the data range in your sheet. 

Remove all empty columns –  This will remove all empty columns from the data range in your sheet. 

Remove all unused rows –  This will remove all unused rows from the sheet after the last row in your data range. 

Remove all empty rows –  This will remove all unused columns from the sheet after the last column in your data range. 

Do this in All sheets checkbox – If you select this checkbox, then the action will be applied in all sheets of the current spreadsheet.

Unpivot Table

Using this tool, you can unpivot an existing pivot table in a few clicks.

To use this tool, from the homepage of the add-on, click on “Unpivot Table” under the General section. Once you have the “Unpivot Table” page is open, follow these steps:

Steps:

1. First select the pivot table and click on “Get Range”
2. Then choose the number of fixed columns and rows (For first timers, leave them to default value and check the result. Then modify as per need)
3. Click on “Apply” button to make the unpivot table a regular flat table

Flip cells

Using this tool, you can alter the position of rows and columns in your sheet.

To use this tool, from the homepage of the add-on, click on “Flip cells” under the General section. Once you have the “Flip cells” page is open, follow these steps:

Steps:

1. Select range – First you need to select the range where you want to flip the cells.

In this example I want to switch the Genre_2 and Genre_3 Column values with each other. So I will select the range of values in these 2 columns.

2. Next choose the option to either flip rows or columns and click “Apply” to see the result.

As I am flipping the column, I will choose the “Flip columns” option and click on the “Apply” button.

Unmerge Cells

Using this tool, you can unmerge all merged cells in your sheet.

To use this tool, from the homepage of the add-on, click on “Unmerge Cells” under the General section. Once you have the “Unmerge Cells” page is open, follow these steps:

Unmerge in selected range: This will unmerge the cells only in the selected range of cells. To use this, first select a range in your sheet, choose the “Unmerge in selected range” option and click on the “Apply” button.

Unmerge in entire sheet: This will unmerge all merged cells in the entire sheet.