Google sheets questions and answers

Here are top questions related to Google Sheets.

FORMATTING

How to move a column

Just take mouse over column name and then drag column and you should be able to move single column. For moving multiple columns, you need to press shift key and then select 2 columns and then drag them. Keep shift key pressed while you drag columns and columns need to be contiguous for this to work

How to pick from drop down list

You can use data validation and in criteria you can use drop down.

How to add page break

When printing excel, you can add custom page breaks to adjust the way data from sheet is printed.

How to add quotes around text

You can add quotes in 2 ways.

By using custom format and using "
By using concatenation operator e.g. =CHAR(34)&B3&CHAR(34)

For dates and money values, you can use text function to format the values.

e.g. =CHAR(34)&text(B4,'dd/mm/yyyy')&CHAR(34) e.g. =CHAR(34)&text(B5,'$#,##')&CHAR(34)

How to show phone numbers in Google Sheet properly

Phone numbers that start with + symbol are not shown correctly in Google Sheets So to fix this, you can start the number with ' symbol

How to remove space before text - How to remove leading spaces

You can remove leading space by 2 ways

Trim function - But drawback of this is that it also removes all leading, trailing as well as duplicate white spaces in the middle of text as well. You can do this by using function or by going to "Data -> Clean up -> Remove whitespaces" menu
By using APP script - By using this method, you can remove only leading spaces.

function TrimLeft() {
    var spreadsheet = SpreadsheetApp.getActive();
    var cell = spreadsheet.getRange("C14");
    var value = cell.getValue();
    var trimmedValue = value.trimLeft(); //trimLeft() is Java Script function.
    cell.setValue(trimmedValue);
}

How to format data as table in Google Sheet

Select the data you want to format as a table
Then go to "data" and then "create filter"
You can show table data in different ways using "Filter Views"
Finally you can add alternating colors to make table look nice

In Microsoft excel, If you go to Home -> Format as a table, it will do all above steps for you automatically.

How to remove table format in Google Sheet

If you have added filters, remove them
If you have added filter views, remove them
If you have added alternating colors, remove them

Difference between add filter and filter views

Add filters will show dropdowns for every column
Then we can add filter views to show data in different ways. e.g. You can add filter view where it will show data for only specific customer.

How to keep leading zeroes

When you add number with leading zeroes, the cell is formatted as "Number - automatic" That's why leading zeroes are removed. To keep leading zeroes, you can format it as "Number - Plain text"

How to join 2 cells in Google sheet

using & operator e.g. =C19 & " " & D19
using concatenate function - e.g. =CONCATENATE(C19,D19)

How to format numbers, date, currency in Google Sheets

You can go to format -> number

How to group rows in Google sheet

Select rows
Right click and then choose "Group rows" or you can use shortcut key "Ctrl+Alt+1"

How to ungroup rows in Google sheet

Select rows
Right click and then choose "Ungroup rows" or you can use shortcut key "Ctrl+Alt+Shift+1"

How to freeze multiple rows - How to use header and bring it down

Select cell then go to "view" and "freeze"

How to zoom out in google sheets - zoom shortcut in google sheets

2 ways in which you can zoom in or zoom out in google sheets

Chane browser zoom level
Change zoom level from google sheet - view -> zoom

How to remove hyperlink and keep only text

You can copy the range that conatins hyperlinks and then paste values only

How to write vertically in google sheets - How to turn text sideways in google sheets

Go to Format -> Rotation

How to wrap text in google sheets

Go to Format -> Wrapping

How to view hidden rows in google sheets - unhide columns or rows

When the rows or columns are hidden, you see arrows near row or columns heading. Just double click on it.

How to do conditional formatting

You need to follow 4 steps

Format -> conditional formatting
Specify range to which formatting should be applied
Specify the format rule when formatting should be applied. e.g. cell value is less than 10 or you can also add custom formula which should return true or false. if condition is true, format is applied
Specify the format properties

Create a custom function in google sheet

Follow below steps

Open sheet
go to "Extensions" -> "App Script"
Write logic for script and save script as shown in below example

/**
 * returns the cube of number.
 * @param {number} input The number to cube.
 * @return The input multiplied by itself 3 times.
 * @customfunction
*/
function CUBE(input){
  return input*input*input
}

function F1(input) {
   let result = ""
   for (let i = 0; i < input.length; i++) {
        for (let j = 0; j < input[i].length; j++) {
           result = result + input[i][j].toLowerCase();
        }
    }

  return result
}

If you are passing single cell value as param, you can access it easily using parameter name
If you are passing range as param, you can access it using 2 dimensional array syntax.
Comments at the top of function are important.
Now go to google sheet and start using the custom function e.g. F1()

How to count same colored cells in Google Sheets and take sum of those cells

There is no built in function for this. But you can write a custom function as explained here

function colorMacro() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();

  var rangeA1Notation = "B62:B64"
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var colorCellA1Notation = "D62";
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();

  var count = 0;
    var total = 0;

  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        {
          count=count+1;
          total=total + values[i][j]
        }

activeSheet.getRange("D65").setValue(count)
activeSheet.getRange("D66").setValue(total)
};

How to assign a macro to button - execute macro after clicking on button

Create a button using drawing tools in Google sheet
Right click on button and assign the script

How to filter by color

You can do this by creating a filter for the data and then click on dropdown for specific column. Then you can filter by specific fill color available in particular column. This is an inbuilt feature in Google Sheets!

How to underline in google sheets

Go to format -> Text -> Underline

How to unmerge cells in google sheets

Go to format -> unmerge

How to title columns in google sheets

You can use named range to title the columns

zip code format in google sheets

You can use custom format like 00000-0000 to set zip code format in Google Sheet

How to tab in a cell in google sheets

You can not add tab key inside a cell but you can add space easily for any range by using formul like =" " & A1 So in this case what will happen is blank space will be added before value in cell A1

How to split a cell in half in google sheets

You can use merge and unmerge feature

How to return in a cell in google sheets

Press ALT + Return key to go to next line within a cell

How to resize all columns in google sheets

Select all cells and then you can resize one column. This will be applied to all columns.

How to resize all rows in google sheets

Select all cells and then you can resize one row. This will be applied to all rows.

How to escape quote in google sheets

You can use this function CHAR(34)

How to paste multiple rows in google sheets

First create empty rows and then copy and paste the rows. It is important to create empty rows because when you paste the rows, rows are not inserted but are pasted in existing rows.

How to paste with formatting in google sheets

You can paste and then select paste format only

How to paste values only in google sheets

You can use ctrl + shift + v

How to protect cells in google sheets

Right click on cell or range of cells and then select protect range option. Once cells are protected, only specific people can edit cells.

How to open where you left off in google sheets

Currently there is no way to open where you left off.

How to outline a cell in google sheets

You can use format border option

How to number rows in google sheets

You can use auto increment formula or use sequence function!

How to name series in google sheets

You will need to change the header value and that changed value will be reflected in the graph series as well.

How to name a column in google sheets

You can define range.

How to name a range in google sheets

You can select range and then right click and select "more cell actions" and then Define range!

How to merge two columns in google sheets

How to lock cells and sheets in google sheets

How to order by number in google sheets

You can select the data to be ordered and then go to data and sort range option.

How to limit rows in google sheets

How to link cells in google sheets

How to lock a row in google sheets

How to lock columns in google sheets

How to add bullet points in google sheets

How to bold lines in google sheets

How to alphabetize in google sheets

How to insert page break in google sheets

How to insert image into cell in google sheets

How to change currency in google sheets

How to change cell size in google sheets or increase cell size in google sheets

How to create filter view in google sheets

How to convert text to number in google sheets

How to rename columns in google sheets

How to delete empty rows in google sheets - Remove blank rows in google sheets

How to delete duplicates in google sheets - Remove duplicates

How to delete rows in google sheets or How to get rid of lines in google sheets

How to delete multiple sheets in google sheets

How to remove gridlines in google sheets

How to duplicate a sheet in google sheets

How to extend columns in google sheets

How to expand cell to fit text in google sheets

How to expand rows in google sheets

How to pin a row in google sheets

How to highlight text in google sheets

How to print the selection and format it

How to print long rows of data

How to highlight every other row in google sheets

How to highlight duplicates in google sheets

Excel Tutorial

MATH and FORMULA RELATED

How to calculate average

How to calculate mean value

How to calculate percentage

How to calculate percentile

Query function

Relative and absolute cell references

Significance of $ in formula

All functions

https://support.google.com/docs/table/25273?hl=en e.g. Query function

Explain google finance function

How to get serial number

How to find circular reference

How to compare two columns

How to calculate total

How to convert text to number

How to count occurrences of items in a list

How to write a formula in google sheets

How to lookup multiple values

How to link cells

How to use if function in google sheets

string functions - e.g. left function

How to use vlookup in google sheets

xlookup multiple criteria in google sheets

How to round up in google sheets

How to protect formulas in google sheets

How to add numbers in a column in google sheets

How to sum a column in google sheets - How to total a column in google sheets

How to multiply cells in google sheets

How to multiply two columns in google sheets

lbs to kg in google sheets

How to transpose in google sheets - How to flip rows and columns

query to count in google sheets

How to enter formula in google sheets

How to do math in google sheets

How to divide a cell in google sheets

How to lock a cell in formula in google sheets

How to add up a column in google sheets

How to add drop down in google sheets

yes no drop down in google sheets

named cells

How to add multiple columns in google sheets

How to apply formula to entire column in google sheets

How to copy formula down or fill down in google sheets or drag formula down in google sheets

How to make all caps

How to make first letter capital

yahoo finance in google sheets

How to vlookup in google sheets

How to drag numbers in google sheets

How to count cells with specific text in google sheets

How to calculate percentage in google sheets

How to find or check for duplicates in google sheets - Identify duplicates

How to merge cells in google sheets

How to make columns same width in google sheets

How to make cells bigger in google sheets

How to fit text to cell in google sheets - Make text fit in cell in google sheets

How to format as table in google sheets or insert table in google sheets

How to get data from another sheet in google sheets

How to link to another sheet in google sheets

How to get sum of column in google sheets

How to import data from another sheet in google sheets

How to link to another tab in google sheets

How to pull data from another sheet in google sheets

How to pull data from another sheet based on criteria in google sheets

How to query another sheet in google sheets

How to reference another sheet in google sheets

How to take data from another sheet in google sheets

How to use data from another sheet in google sheets

How to break external data links

if formula

DATA SORTING and FILTERING

Load data from external sources or api

How to get unique values

How to sort query in google sheets

How to put in alphabetical order in google sheets

How to sort by date in google sheets

How to calculate time difference

How to calculate years between two dates

How to get day from date

How to get month and year from date

How to sort alphabetically in google sheets

How to keep rows together when sorting in google sheets

How to filter multiple columns in google sheets

How to filter in google sheets

How to edit pivot table in google sheets

How to refresh pivot table in google sheets

How to open pivot table editor in google sheets

How to edit drop down list in google sheets

How to sort by date in google sheets

date to quarter in google sheets

How to sort by number in google sheets

How to sort by value in google sheets

How to sort by column in google sheets

How to sort by multiple columns in google sheets

DATE and TIME

How to add time

How to organize by date in google sheets

How to insert date in google sheets

How to add dates in google sheets

How to add months to a date in google sheets

How to autofill dates in google sheets

How to organize by date in google sheets

How to change date format in google sheets

Prevent the behaviour where Date keeps changing

SCRIPTING

How to record and run macro

How to share macro

All JS functions

upper case, lower case, leftTrim, rightTrim, length, instr etc
all date functions
all regular expressions
number formatting and currency