How to Use Google Sheets Like a Pro

Step by step

1. 5 min

   Master keyboard shortcuts for speed

   Use Ctrl+Shift+Arrow keys to select data ranges instantly. Press Ctrl+; to insert today's date and Ctrl+Shift+; for current time. Navigate between sheets with Ctrl+Page Up/Page Down. Press F4 to repeat your last action across multiple cells.

   Note: Create custom shortcuts in Tools > Preferences > General for functions you use repeatedly.
2. 8 min

   Build dynamic formulas with ARRAYFORMULA

   Replace copying formulas down columns with ARRAYFORMULA. Type =ARRAYFORMULA(A2:A*B2:B) to multiply entire columns automatically. Combine with IF statements: =ARRAYFORMULA(IF(A2:A>0,A2:A*B2:B,"")). New rows inherit the formula without manual copying.

   Note: ARRAYFORMULA works with most functions except SUMIF, COUNTIF, and lookup functions.
3. 10 min

   Create pivot tables for data analysis

   Select your data range and go to Insert > Pivot table. Drag dimensions to Rows and metrics to Values. Add filters by dragging fields to Filters section. Change aggregation methods by clicking the Values field dropdown and selecting Sum, Average, or Count.

   Note: Use calculated fields in pivot tables by clicking Add next to Values and selecting Calculated field.
4. 6 min

   Implement data validation for clean input

   Select cells and go to Data > Data validation. Choose List from a range to create dropdowns from existing data. Set Custom formula validation with rules like =LEN(A1)<=10 to limit character count. Check 'Reject input' to prevent invalid entries.

   Note: Use dependent dropdowns by referencing INDIRECT formulas in your validation criteria.
5. 12 min

   Automate tasks with Google Apps Script

   Open Extensions > Apps Script to access the code editor. Write functions that trigger on form submissions, time intervals, or cell edits. Use onEdit() to run code when cells change. Deploy scripts as web apps to create custom interfaces for your sheets.

   Note: Start with simple scripts like automatically timestamping edited rows before building complex automation.
6. 7 min

   Use QUERY function for SQL-like data manipulation

   Apply =QUERY(A1:D100,"SELECT A,B WHERE C > 50 ORDER BY B") to filter and sort data. Combine conditions with AND/OR operators. Use aggregate functions like COUNT, SUM, and AVG within QUERY. Group data with GROUP BY clauses for summary reports.

   Note: QUERY uses Google Visualization API query language, which supports most SQL SELECT syntax.
7. 6 min

   Set up conditional formatting rules

   Select your range and choose Format > Conditional formatting. Create custom formulas like =$B1>AVERAGE($B:$B) to highlight above-average values. Use color scales for gradual formatting across value ranges. Combine multiple rules to create complex visual indicators.

   Note: Reference absolute ranges with $ symbols so formatting rules apply correctly when copied to new areas.