Lock a reference with F4
In a formula select A1, then press F4 to cycle through relative, absolute and mixed references.
=$A$1*B2Explore shortcuts, formulas and workflows to work faster, reduce errors and make better decisions in Excel and Google Sheets.
Shortcuts, formulas, automation, dashboards and collaboration.
In a formula select A1, then press F4 to cycle through relative, absolute and mixed references.
=$A$1*B2Select your table and press Ctrl + Shift + L to toggle filters.
Ctrl + Shift + LCtrl + T converts a range into a table that expands as rows are added.
Ctrl + TSUMIFS totals sales matching several conditions.
=SOMME.SI.ENS(C2:C100;A2:A100;"Paris";B2:B100;"Payé")Look up a value in another column with an explicit not-found result.
=RECHERCHEX(E2;A2:A50;C2:C50;"Introuvable")FILTER returns only the rows matching a condition.
=FILTRE(A2:D100;D2:D100="En retard";"Aucun résultat")UNIQUE returns a dynamic list of distinct values.
=UNIQUE(A2:A100)SORT updates a sorted list without editing the source.
=TRIER(A2:C50;2;-1)LET names intermediate calculations to make a formula easier to maintain.
=LET(total;SOMME(B2:B20);total*0,15)Data > From Text/CSV: import, transform and refresh recurring files.
Données → Obtenir des donnéesInsert > PivotTable to summarize rows by month, category or sales person.
Insertion → Tableau croisé dynamiqueData > Data Validation creates drop-down lists of allowed values.
Données → Validation des donnéesUse conditional formatting to flag overdue dates and pending statuses.
=$B2<AUJOURDHUI()View > Freeze Panes keeps headers visible while scrolling.
Affichage → Figer les voletsDivide gross profit by revenue and guard against zero revenue.
=SI(B2=0;0;(B2-C2)/B2)Compare a due date to today without showing negative overdue days.
=MAX(0;AUJOURDHUI()-B2)Ctrl + G opens Go To to navigate to a named range or cell.
Ctrl + GUnlock inputs, then use Review > Protect Sheet. It prevents accidental edits but does not secure confidential information.
Révision → Protéger la feuilleGoogle Sheets QUERY lets you filter and aggregate rows with SQL-like syntax.
=QUERY(A1:D100;"select A, sum(D) group by A";1)IMPORTRANGE pulls a range from another spreadsheet after authorization.
=IMPORTRANGE("URL_DU_CLASSEUR";"Feuille1!A1:D20")ARRAYFORMULA applies a calculation across multiple rows.
=ARRAYFORMULA(SI(A2:A="";"";B2:B*C2:C))SPARKLINE draws a mini-chart in a single cell.
=SPARKLINE(B2:G2)Use comments and mentions to request reviews without changing data.
Insertion → CommentaireTRIM removes excessive spaces after data imports.
=SUPPRESPACE(A2)Function names and argument separators may vary by app language and locale.
Join our public forum. Ask a question, share a formula or teach your favorite workflow.