If you don't spend hours every day working in spreadsheets, you may know only the basics of what Google Sheets can do: add, subtract, and sort data. But Sheets has a lot of powerful features that make data management simpler for even the casual user. Here are 10 hidden Google Sheets tricks everyone should know.
Edit shareable links in Google Sheets to make documents into clean copies or downloadable CSVs
One of the best features of Sheets and other Google Workspace apps is that collaborators can make and view changes in real time. In some cases, though, you may not want to share your live spreadsheet, instead providing a clean copy or a downloadable CSV. With editable sharing links, you don't need to make copies or export manually. Simply replace /edit at the end of the link with /copy (for a clean duplicate) or either /export?format=pdf or /export?format=csv (for file downloads).
Create cell links to direct collaborators to specific cells or data blocks
When you want collaborators to review a specific part of a large dataset, you don't need to provide a cell number or range for them to find manually. Instead, send them a link directly to an individual cell or cell block. Highlight the cell or cell range, right-click, and select View more cell actions > Get a link to this cell/range. The link will be copied to your clipboard so it can be easily shared.
Create in-cell mini charts in Google Sheets to see trends
Sheets has powerful data visualization features, but if all you need is a simple trend line, you can do that within a cell for an at-a-glance look at data over time. This also keeps your spreadsheet clean, so you don't have multiple charts and graphs overlaying the rest of your data. Type =SPARKLINE into a cell, select your range, and hit Enter.
Apply conditional formatting’s custom gradients to see high and low values at a glance
Conditional formatting applies colors to your cells based on specific rules—for example, color-coding project statuses or deadlines based on due dates or turning negative currency amounts red while positive amounts display green. An extra layer of conditional formatting is custom gradients, which shades values based on a scale so you can see high and low ends of the range at a glance. To set this up, go to Format > Conditional formatting > Color scale and select the cell range, min and max values, and color gradient. Then press Done.
Use Smart Chips to insert interactive elements and Drive files for live collaboration
Smart Chips are interactive, clickable elements in Google Workspace that allow you to tag contributors, link files, insert tables or status dropdowns, or quickly add dates and emojis to your spreadsheet. When you add a Drive file, for example, clicking the cell will pull up a preview of and link to the document, which is far cleaner than copying and pasting the full URL. Simply type "@" into any cell to pull up the Smart Chip menu.
Set up custom terms and dropdown shortcuts in Sheets to simplify typed inputs
While Sheets doesn't have the same native text substitution feature as Docs (allowing you to set up shortcuts that automatically turn into longer text), there is a workaround using dropdowns. This way, you can quickly add longer inputs that you frequently find yourself typing out and formatting, such as addresses, phone numbers, category labels, and brand names. Go to Insert > Dropdown and enter the text you want to appear in the Option box. You can add multiple choices and color code them as well. (Alternatively, you can use the Smart Chip hack above and select Dropdown from the menu.)
Another text tweak in Sheets and across Workspace is the personal dictionary, which keeps Google from marking words as spelling errors. Add brand-specific terms, names, and other commonly used words and phrases via Tools > Spelling > Personal dictionary.
Use IMPORTHTML to scrape and auto-refresh web data
If you want to pull publicly available data into your spreadsheet but don't want to go through the potentially tedious process of copying, pasting, and reformatting, use the IMPORTHTML command to have Sheets do all of that for you. Paste =IMPORTHTML("url", "query", "index") and fill in the URL of the page you want to pull from, whether it's a "table" or "list" in the query field, and the table number on the live page. The data refreshes each time you open or refresh your spreadsheet, so you don't have to update them manually. Note that this function only works on public websites that don't require a login to access.
Turn on data cleanup to remove duplicates and extra spaces from raw data imports
If you do need to import raw data from external sources (like CSVs) into your spreadsheet, Sheets can take care of some of the cleanup, so you don't have to manually remove duplicates from a huge dataset. Go to Data > Data Cleanup and select from Remove duplicates, Trim whitespace, or Cleanup suggestions.
Link your spreadsheet to a Google Form to organize responses
Google Forms feed responses into Sheets, but you can also go in reverse and link a new form to your existing spreadsheet. For example, within your master budget spreadsheet, you can create a new tab for each month, and then use a Google Form with basic questions like transaction date, amount, and expense type to submit charges that autopopulate back to that tab instead of dealing in the spreadsheet directly. Go to Tools > Create a new form. This will pull up a form response tab in your spreadsheet (which can be renamed for the specific month) and an editable Google Form. Note that you'll need to publish the form in order to use it. If you have Workspace AI features, you can also have AI generate the form for you.
Use Google Translate commands to quickly translate cell contents
Sheets has a built-in Google Translate function, which allows you to convert text from one language to another without copying and pasting into another tool. Use this when you travel to create a dynamic, side-by-side phrase dictionary or apply it to a Google Form feedback sheet that receives inputs in multiple languages. Paste the command =GOOGLETRANSLATE(cell, "auto", "target_language") in which "auto" is the current language ("en" for English, for example). Then, highlight and drag across the row or down the column as needed.
Edens.nl: het laatste nieuws het eerst! 
