Google Sheets is Google's Microsoft Excel killer. It's a spreadsheet program that's included as part of the Google Docs web-based software office suite. It has many useful features which are often overlooked but which, when used correctly can greatly improve productivity in the workplace. Read on to find out more.
The program has many keyboard shortcuts, but the most notable one is the combination of commands that you can use to quickly insert the current time and/or date. To add the current date, press Cmd (Mac) or Ctrl (Windows) plus the semicolon key. If you want to add time, simply hit Cmd or Ctrl and Shift plus the semicolon key. If you want to add the time and date together, press Cmd or Ctrl together with Alt and Shift plus the semicolon key.
The program also comes with fast-formatting shortcuts that can make you more productive at work. Google Sheets allows you to format a cell or even a selection of cells without digging around in lots menus. Here are some useful examples:
- To format as percentage, press Ctrl-Shift-5
- To format as currency, hit Ctrl-Shift-4
- To format as date, press Ctrl-Shift-3
- To format as time, hit Ctrl-Shift-2
- To format as decimal, press Ctrl-Shift-1
Quick tip: Creating a new sheet
Google rolled out a time-saving shortcut for users who want to open a new Google Sheets spreadsheet. Simply go to your browser's address bar and type sheet.new, spreadsheet.new or sheets.new. Typing any of these options will start a new spreadsheet as long as you have signed into your existing Google account.
Personalised shortcuts / macros
You can also create your own personalised shortcuts or macros from within Google Sheets. To create a macro
- Open the Tools menu,
- select Macros,
- select Record macro.
- Select Use absolute references if you would like this shortcut to be performed on the same cells every time. Otherwise, you should select Use relative references.
- Once you have done this, perform the action(s) you would like to record.
For instance, you can set specific type of formatting for the contents in a cell. You can also manipulate data by copying the contents in a cell, then erase the cell and paste the contents a cell over to your left.
Once you're finished. Press the Save button located in the macro-recording panel, name your new shortcut and assign it to one of the available key combinations for future use.
Mini inline graphs
Sheets' nifty Sparkline feature allows you to create a small chart within a cell. Type this command =SPARKLINE, then the cells that you would like to include followed by the word "charttype. Then, type `the type of chart you would like to create; for instance, column, bar or line-formatted. You can even include various customisation commands to control the colours that are used in different sections of your chart plus other visual factors.
Perform fast calculations
You can perform calculations quickly in any number-oriented spreadsheets. Just highlight several cells then look in the lower-right side corner of your screen. Sheets will show the total of the numbers that you have selected by default. You can click the box showing this information and tell it to display the maximum, minimum or the entire count of numbers involved. After making this change, the selection will remain as the new default for future calculations that you will perform.
Exploit Google's artificial intelligence
You can tap into artificial intelligence and let the program perform different kinds of data analysis and produce complex charts for you. Just take your mouse and hover it over that starburst-shaped icon located in the lower-right side corner of your screen; the word "Explore" will appear. Click this button and a panel of information related to your data will appear. You are able to highlight specific rows in the spreadsheet and change its focus. You can also hover over the items it presents and find options for inserting or adjusting it in your sheet.
Copying cell formatting
The program allows you to quickly copy the complete set of formatting of a cell and then apply it to any other cell you want with just a few clicks. To do this, click the cell that has the formatting you would like to copy. Next, go to the paint roller icon located to the right side of the print command and click it. Click the cell where you would like the formatting to apply. Once you do this, everything from the colour and font size to the numerical style and cell shading will carry over.
With Sheets, you can hide any row that you like. Go to the grey column located at the left of your screen, then click its number. A menu will appear; select "Hide row". To show this row again, simply press the black arrows that will appear in its position within the same column located at the left of your screen.
Rotate text in your header
Additionally, Sheets can rotate your text in the header row. Just highlight the row and click on the icon showing an angled A, and has an up-pointing arrow below it. Pick from several striking effects and set your header apart to give your spreadsheet an attractive polished appearance.
Create beautiful dashboards
Sheets also comes with a fast and easy way of making a spreadsheet pop. Go to the Format menu and find the "Alternating colours". This will give you a set of options that will give your rows a sharp-looking colour pattern.
For some examples of the dashboards you can to manage your key performance indicators (KPIs), check out our blog series - creating dashboards with Okappy and Google Sheets.
Sharing and collaborating
After sharing a spreadsheet, within specific people or publicly, you can create a link that will allow those with access to quickly copy your spreadsheets into their Google account. They can use them as templates or modify them without affecting the original versions. Simply copy the URL in the web browser's address bar while viewing the spreadsheet, then change the word edit and put copy. When those with access to this spreadsheet opens the link, they will be instantly prompted to copy the spreadsheet.
Export your spreadsheet
Sheets allows you to export spreadsheets in different formats through the "Download as" option located in the File menu. However, if you want to give people at your workplace a direct link so that they can download your data in PDF format, simply copy the URL of your spreadsheet as aforementioned, but remove the word "edit" and put "export?format=pdf". As soon as those with access to your spreadsheet open the link, they will immediately see PDF export.
Receive notifications whenever your sheet is edited
When using forms to accept any survey-like responses or sharing a spreadsheet, you can ask this program to notify you when there is an addition or edit, either as a once-every-day email digest or immediately. There is a "Notification rules" option located in the Tools menu where you can set your preferences for a specific spreadsheet.
Sheets allows you to work more efficiently and improve productivity by managing your time, making your tasks at work easier and greatly increasing the speed at which you work.