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.