Excel Tips and Productivity Guide
Over 80 practical shortcuts and techniques to speed up your workflow, simplify data management, and take you one step ahead in Excel.
1. Shortcuts and Acceleration (20 Tips)
Table Selection: Ctrl + A
Instantly selects the entire data block. Pressing twice selects the entire worksheet.
Create Table: Ctrl + L (or T)
Converts your data range into a smart table format. Critical for formulas and filtering.
Quick Fill Down: Ctrl + D
Fills the formula or value in the selected cell downward into the entire selected range. (Copies downwards)
Percentage Format: Ctrl + Shift + %
Quickly converts selected numbers to percentage format (%0.00).
Current Date: Ctrl + ;
Adds the current date (static) to the cell instantly.
Add Comment: Shift + F2
Allows you to quickly add a note (comment) to the selected cell. Essential for project tracking.
Filter On/Off: Ctrl + Shift + L
Instantly enables or disables filters in the selected range.
AutoSum: Alt + =
Automatically sums the number block above the selected cell using the SUM formula.
Edit Cell: F2
Activates the formula bar without entering the cell (without double-clicking).
Undo/Redo: Ctrl + Z / Ctrl + Y
Undoes the last action (Undo) or redoes it (Redo).
Format Painter: Alt + H + F + P
Copies the formatting (color, font) of one cell and applies it to another.
Show Formulas: Ctrl + \` (Grave Accent)
Shows the formulas themselves in the cells instead of the formula results.
Go to Last Cell: Ctrl + End
Instantly jumps to the last cell containing data.
Insert Chart: Alt + F1
Instantly creates the default chart type from the selected data.
Move Cell: Ctrl + Arrow Keys
Quickly jumps to the edge of the current data block.
Delete Row/Column: Ctrl + -
Shortcut to delete the selected row or column.
Current Time: Ctrl + Shift + ;
Adds the current time (static) to the cell instantly.
Print Preview: Ctrl + P
Opens print settings and preview.
New Workbook: Ctrl + N
Opens a new and empty Excel workbook.
Save: Ctrl + S
Saves the workbook.
2. Formula and Function Mastery (20 Tips)
Absolute Reference Lock: F4
Pressing F4 while typing a formula makes the cell Absolute ($A$1), preventing it from changing during copying.
XLOOKUP Usage
Use XLOOKUP instead of VLOOKUP. It can search from the left and is not affected by adding columns.
Data Validation (Lists)
Increases data consistency by allowing only values from a predefined list to be entered into the cell.
Error Trapping: IFERROR
Allows you to display a custom message (e.g., "No Data") instead of an error (e.g., #VALUE!) when your formula returns an error.
Dynamic Array Formulas
Thanks to the SPILL feature, you can now automatically populate results into multiple cells with a single formula (e.g., SORT, UNIQUE).
Summation Limits: SUMIFS
Use SUMIFS instead of standard SUMIF to sum values that meet multiple criteria (like Date, Region, Product Name).
Text Concatenation: & Sign
Use the `&` sign instead of CONCATENATE to combine text in a formula. E.g.: `=A1 & " " & B1`.
Name Manager
Name frequently used ranges (E.g.: 'Database', 'Costs') and use their names in your formulas instead of cell references.
INDIRECT Usage
Used to convert a text written expression into an actual cell reference. Critical for advanced dashboards.
FILTER Function
Dynamically filters a range with a single formula, returning results without the need for conditional formatting.
Date Control: DATEDIF
Used to accurately calculate the difference in days, months, or years between two dates.
LOGICAL Operators
Combine multiple logical conditions in one formula using the AND and OR functions.
Text Formatting: UPPER
Convert all text in a cell to uppercase with UPPER, and to lowercase with LOWER.
Text Splitting: LEFT, RIGHT
Separates a specified number of characters from the left or right of a text string.
RANDBETWEEN
Used to generate a random integer between the lower and upper bounds you set.
Sorting: SORT
As a dynamic array formula, it sorts data from a single cell and automatically spills the results.
Percentage Difference: (New Value - Old Value) / Old Value
The fastest way to calculate the percentage difference between two values.
Investment Value: FV (Future Value)
Calculates the future value of an investment with fixed payments at fixed intervals.
CLEAN Function
Removes all non-printable characters from text (errors that often occur in external data transfer).
COLUMN and ROW Functions
Used to automatically increment the column/row index in dynamic formulas or VLOOKUP functions.
3. Data Management and Cleaning (20 Tips)
Quick Filtering: Alt + Down Arrow
Pressing this shortcut while in a cell instantly opens the filter menu for that column.
Text to Columns Conversion
Use the Data > Data Tools > Text to Columns tool to split data separated by commas, periods, or spaces (E.g.: "Firstname Surname") into different columns.
Remove Duplicates
Clean duplicate records from your list in seconds using Data > Data Tools > Remove Duplicates.
Flash Fill: Ctrl + E
Shows a single example for Excel to recognize the data pattern and automatically fills the column (E.g.: Separating only the surname from the full name).
Go To: Ctrl + G
Used to quickly jump to specific cells, named ranges, or empty cells on your worksheet.
Cleaning Spaces: TRIM
Removes all unnecessary spaces (except for a single space between words) from cells, essential for externally sourced data.
Paste Values: Ctrl + Alt + V, V
Pastes only the formula results (values), not the formulas or formatting. Important for debugging.
Grouping Dates (Pivot)
You can automatically group data by Year, Quarter, or Month by selecting the date field in Pivot Tables, right-clicking, and choosing "Group".
Hiding Zeros
Hide zero values by unchecking the "Show a zero in cells that have zero value" option under Settings > Advanced.
Row/Column Swap (Transpose)
Use the "Transpose" option under the "Paste Special" menu when pasting copied data to swap rows and columns.
List Comparison: COUNTIF
Use COUNTIF to quickly find matching or missing items between two lists (columns).
Writing Format: PROPER
Makes the first letter of every word in the text capitalized, and the rest lowercase (E.g.: "ali veli" -> "Ali Veli").
Store as Text: ' (Single Quote)
Ensure the format remains text by placing a single quote (') at the beginning when entering a number or date into a cell.
Table Naming
Give your tables (created with Ctrl+L) meaningful names (E.g.: 'SalesData') and use them easily in formulas.
Column Widening: Double Click
Automatically adjust the column width to the content by double-clicking between the two column headers.
External Data Acquisition: Power Query
Use the Data > Get Data menu to import data from different sources (CSV, SQL, Web); never copy-paste.
Invalid Data Check
Use "Circle Invalid Data" under the Data tab to find cells with errors in your Data Validation rules.
Merging Cells: MERGE AND CENTER
Do not only use 'Merge and Center' for centering titles; for better presentation, use 'Format Cells > Alignment > Horizontal: Center Across Selection'.
Clear Formatting
Remove only style remnants using the "Clear Formats" option under the "Clear" menu on the Home tab.
Hide Columns: Ctrl + 0
Quickly hide the selected columns. (To unhide, select adjacent columns and try Ctrl+Shift+0).
4. Analysis and Visualization (20 Tips)
Quick Pivot Table: Alt + D + P
This shortcut initiates the Pivot Table creation wizard when your data range is selected.
Conditional Formatting
Use Conditional Formatting to color-code your data (E.g.: making the top 10% green) and quickly see trends.
Recommended Charts
The 'Recommended Charts' option under the Insert tab automatically suggests the most suitable visualization type for your selected data.
Goal Seek
Calculates which input needs to change for a formula result to reach a specific value (E.g.: How many products must I sell to achieve 10,000 TL profit?).
Slicers
Used to filter Pivot Tables and Tables with interactive buttons, making reports user-friendly.
Solver Add-in
An advanced optimization tool used to maximize or minimize a goal under complex constraints.
Scenario Manager
Used to create and save results from different "What-If" analyses (Optimistic, Pessimistic, Normal scenarios).
Using Secondary Axis
Used when displaying two different data series with large differences (E.g.: Quantity and Revenue) on a single chart.
Data Bars
Adds small colored bars inside the cells, showing the magnitude of the value using Conditional Formatting, providing quick visual analysis.
Pivot Table Detail
Gain instant access to the detailed source data that makes up the total by double-clicking any total value on the Pivot Table.
Pie Chart Limits
Use pie charts only for data series that sum up to 100% (no more than 5-7 segments). More than that becomes unreadable.
Sparkline Charts
Add mini charts (Sparklines) next to the data set, showing the trend within a single cell (Insert tab).
Calculated Field (Pivot)
Use the 'Calculated Field' feature in the Pivot Table to multiply or divide two different fields.
Timeline
Use the 'Insert Timeline' feature under Pivot Table tools to filter date fields.
External Data Sources (Power Pivot)
Use Power Pivot to feed your reports from multiple sources (Excel, Access, or SQL) rather than just one Excel table.
Sorting by Value (Pivot)
Sort the row labels in your Pivot Tables by their total values (Largest to Smallest), not just alphabetically.
Format Painter (Conditional)
You can use the Format Painter to copy Conditional Formatting rules from one range to another.
Watch Window
Used to instantly monitor cell values across different sheets or workbooks in a single window (under the Formulas tab).
Quick Analysis
Create Conditional Formatting or Charts instantly using the icon that appears at the bottom right corner of a selected data block.
Pivot Table Report Filter
Use the Pivot Table Options > Show Report Filter Pages option to create a separate worksheet for each filter item.