My list is just a quick summary of PCMag’s article on “these 47 Excel tricks will transform you into a power user”. You can quickly peruse my list to see if something looks interesting. Go to the article for more information.
These 47 Expert Excel Tricks Will Transform You Into a Power User
Here are the 47 Excel tricks:
- Select Everything, Everywhere, All At Once
Quickly select the entire worksheet by clicking the small box in the top-left corner above row 1 and left of column A (or use Ctrl+A) to grab all data instantly. - Paint Cells to a New Format
Use the Format Painter (paintbrush icon on the Home tab) to copy formatting like font, color, and alignment from one cell to others—double-click it to apply the style to multiple cells at once. - Add Line Breaks and Wrapping Text
Insert line breaks within a cell using Alt+Enter, or enable Wrap Text on the Home tab to automatically wrap text at cell edges for better readability. - Autofit All the Columns/Rows Instantly
Select everything with Ctrl+A, then use Alt+HOI to autofit column widths and Alt+HOA to autofit row heights so all content becomes fully visible without manual dragging. - AutoFill Your Cells
Drag the small plus-sign handle in the bottom-right corner of a cell to automatically continue patterns like dates, numbers, or days of the week across rows or columns. - Flash Fill: Fastest Fill Alive
Type an example of the desired format in a new column (e.g., reformatting phone numbers), and Excel intelligently predicts and fills the rest—press Enter or use the Data tab’s Flash Fill button to accept. - Ctrl+Shift to Select
Hold Ctrl+Shift and press arrow keys to rapidly select entire columns, rows, or contiguous data blocks containing values, jumping to the last filled cell. - Drag That Data Around
Select a column or range, hover over the edge until the move cursor appears, then drag to relocate it—or hold Ctrl to copy instead of moving. - Text to Columns
Split data from one column into multiple ones (e.g., first/last names or CSV values) using the Data tab’s Text to Columns wizard, choosing delimiters like spaces/commas or fixed widths. - Paste Special to Transpose
Copy data, then use Paste Special > Transpose to flip rows into columns or columns into rows without manual re-entry. - Multiple Cells, Same Data
Select multiple cells (using Ctrl or drag), type your entry or formula, and press Ctrl+Enter to fill the exact same content into all selected cells at once. - Paste Special With Formulas
Copy a value like 100, select target cells, and use Paste Special > Divide (or Add/Subtract/Multiply) to quickly scale numbers, such as converting decimals to percentages. - Save Charts as Templates
Perfect a chart, right-click it, and save as a template (.CRTX file) so you can reuse the exact style, colors, and effects on future charts via the Templates folder. - Use Graphics in Charts
Insert pictures or textures into chart elements like bars or pie slices via Series Options > Fill to add visual flair, such as logos or icons. - Work With Cells Across Worksheets
Use 3D references in formulas (e.g., =SUM(Sheet1:Sheet10!B3)) to calculate across identically structured sheets, like summing the same cell over multiple years. - Hide in Plain Sight
Make cell contents invisible (but still usable in formulas) by formatting them as Custom type with three semicolons (;;;). - Hide a Whole Sheet
Right-click a sheet tab to hide it (keeping data available for formulas), then use View > Unhide to restore it when needed. - Use the Personal Workbook for Macros
Record macros and store them in Personal Macro Workbook (Personal.XLSB) so they’re available in every Excel file you open. - Pivot! Pivot!
Create PivotTables (via Insert tab) to dynamically summarize and analyze large datasets by dragging fields for quick insights and groupings. - Drill Down on PivotTable Sources
Double-click any value in a PivotTable to generate a new sheet showing the exact source rows that contributed to that number. - Slice Into Some Data
Add interactive Slicers (Insert > Slicer) to PivotTables or tables for fast, visual filtering with clickable buttons. - Perform Quick Analysis
Select data and click the Quick Analysis icon (bottom-right) for instant access to charts, totals, conditional formatting, sparklines, and more. - Quickly Customize Your Data Table
Convert data to a formatted, sortable table (Home > Format as Table or Ctrl+T) with built-in styles, filters, and easy design tweaks. - Validate Data to Make Drop Downs
Use Data > Data Validation > List to create drop-down menus in cells, restricting input to predefined options and reducing errors. - Conditionally Format Tables
Apply Conditional Formatting (Home tab) to highlight top/bottom values, duplicates, color scales, data bars, or custom rules automatically. - Screenshot Insertion
Insert screenshots of other open windows directly into your sheet via Insert > Screenshot for quick visual references. - Pull Data from Pictures
Import tabular data from an image or screenshot using Data > From Picture, letting Excel analyze and convert it into editable cells. - Insert Excel Data Into Word
Copy cells or charts and paste into Word/PowerPoint—they stay linked for automatic updates, or paste as a static picture. - Use a Dollar Sign ($) to Prevent Shift
Add $ to cell references in formulas (e.g., $A$1) to create absolute references that don’t change when copying the formula. - Great Excel Shortcut Keys
Master time-savers like Ctrl+; for today’s date, Ctrl+` to show formulas, Ctrl+Shift+V to paste values only, and many more. - Quickly Add Without Formulas
Select multiple cells (hold Ctrl) and view instant sum, average, count, etc., right on the status bar at the bottom. - Freeze Headers for Scrolling
Use View > Freeze Panes to lock header rows/columns in place so they stay visible while scrolling through large datasets. - New Window for Second View
Open a second window of the same workbook (View > New Window) to view and edit different parts simultaneously, great for big screens. - Customize the Quick Access Bar
Add your most-used commands, macros, or tools to the Quick Access Toolbar via File > Options for one-click efficiency. - Combine Text From Different Cells
Use & or the CONCAT function in formulas to join text from multiple cells, adding spaces or separators as needed. - Combine Multiple Workbooks Into One
Import and merge similarly structured files from a folder using Data > Get Data > From File > From Folder. - Link Between Tabs (Worksheets) or Cells
Create hyperlinks within a workbook to jump between sheets or specific cells for easier navigation. - Place Same Data on Every Sheet
Group multiple sheets (hold Ctrl or Shift on tabs), then enter data/formatting—it replicates across all grouped sheets. - Use AI via Copilot in Excel
Leverage Microsoft Copilot (with subscription) to analyze data, suggest formulas, and provide insights directly in Excel. - Find Formulas With Any AI
Ask free or paid AI chatbots (like Copilot, ChatGPT) to generate or explain Excel formulas tailored to your data. - Format Multiple Tabs
Group sheets, apply formatting to one, and it propagates to all selected tabs for consistent styling. - Hide the Zeros
Turn off display of zero values globally via File > Options > Advanced to make sheets look cleaner. - Autosum Columns and Rows
Place cursor below/ beside numbers and press Alt+= to instantly insert SUM formulas for rows, columns, or both. - Master Xlookup
Use the powerful XLOOKUP function (replacing VLOOKUP) for flexible lookups in any direction with better error handling. - Instantly Create Absolute References in Formulas
Highlight a reference in a formula and press F4 to toggle between relative and absolute ($ signs) versions quickly. - Get Ready for the Pilot Function
Use the upcoming =COPILOT() function (in beta for certain subscribers) to run natural-language AI queries directly in cells. - Properly Embed an Image with the IMAGE Function
Insert web-hosted images into cells using =IMAGE(url) so they stay anchored, resize with cells, and move with data (Microsoft 365 only).
There you go. Check out the article for more information on each item.