One of the FASTEST ways to Learn Excel is to learn some of the Excel TIPS and TRICKS, period and if you learn a single Excel tip a day you can learn 30 new things in a month.
But you must have a list that you can refer to every day instead of searching here and there. Well, I’m super PROUD to say that this is the most comprehensive list with all the basic and advanced tips that you can find on the INTERNET.
In this LIST, I have covered 300+ Excel TIPS and TRICKS which you can learn to Level Up your Excel Skills.
(Excel Tip-1) Add Serial Numbers
If you work with large data, then it’s better to add a serial number column to it. For me, the best way to do this is to apply the table (Control + T) to the data and then add 1 in the above serial number, just like below.
To do this, you simply need to add 1 to the first cell of the column and then create a formula to add 1 to the above cell’s value.
As you are using a table, whenever you create a new entry in the table, Excel will automatically drop down the formula and you will get the serial number.
(Excel Tip-2) Insert Current Date and Time
The best way to insert the current date and time is to use the NOW function which takes the date and time from the system and returns it.
The only problem with this function is it’s volatile, and whenever you recalculate something, it updates its value. And if you don’t want to do this, the best way is to convert it to hard value. You can also use the below VBA code.
Sub timestamp()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
End Sub
Or these methods to insert a timestamp in a cell.
(Excel Tip-3) Select Non-Continues Cells
Normally we all do it this way, hold the control key, and select cells one by one. But I have found that there is a far better way for this. All you have to do is, select the first cell and then press SHIFT + F8.
This gives you add or remove selection mode in which you can select cells just by selecting them.
(Excel Tip-4) Sort Buttons
If you deal with data that needs to sort frequently then it’s better to add a button to the quick access toolbar (if it’s not there already).
All you need to do is click on the down arrow on the quick access toolbar and then select “Sort Ascending” and “Sort Descending”. It adds both buttons to the QAT.
(Excel Tip-5) Move Data
I’m sure you think about copy-paste but you can also use drag-drop for this.
Simply select the range where you have data and then click on the border of the selection. By holding it move to the place where you need to put it.
(Excel Tip-6) Status Bar
The status bar is always there but we hardly use it to the full. If you right-click on it, you can see there are a lot of options you can add.
(Excel Tip-7) Clipboard
There is a problem with normal copy-paste that you can only use a single value at a time.
But here is the kicker: When you copy a value, it goes to the clipboard and if you open the clipboard you can paste all the values which you have copied. To open a clipboard, click on the go to Home Tab ➜ Editing and then click on the down arrow.
It will open the clipboard on the left side, and you can paste values from there.
(Excel Tip-8) Bullet Points
The easiest way to insert bullet points in Excel is by using custom formatting and here are the steps for this:
- Press Ctrl + 1 and you will get the “Format Cell” dialogue box.
- Under the number tab, select custom.
- In the input bar, enter the following formatting.
- ● General;● General;● General;● General
- In the end, click OK.
Now, whenever you enter a value in the cell Excel will add a bullet before that.
(Excel Tip-9) Worksheet Copy
To create a copy of a worksheet in the same workbook drag and drop in the best way.
You just need to click and hold the mouse on the sheet’s name tab and then drag and drop it, to the left or right, where you want to create a copy.
(Excel Tip-10) Undo-Redo
Just like sort buttons you can also add undo and redo buttons to the QAT. The best part about those buttons is you can use them to undo a particular activity without pressing the shortcut key again and again.
Excel Tips Series
More Tips and Tricks
- Top 10 Benefits of Microsoft Excel
- Change Tab Color in Excel (Worksheet Tab Background Color)
- Delete a Single Row or Multiple Rows in Excel
- Copy and Paste a Column in Excel
- Zoom In or Zoom Out in Excel
- Move a Row and Column in Excel
- Excel Options (Mac and Windows)
- Add Border in Excel
- Change Border Color in Excel
- Change Column Width in Excel
- Clear Formatting in Excel
- Copy Formatting in Excel
- Dialog Box in Excel
- Freeze Panes in Excel
- Quickly Swap Two Cells in Excel
- What is a Column in Excel
- Row in Excel
- Alignment in Excel
- Unhide Rows in Excel
- Range in Excel
- Ribbon in Excel
- Copy and Paste Column Width in Excel
- Copy and Paste Values Without Formatting in Excel
- Add Dollar Sign in Excel
- Make First Row Header in Excel
- Save a File in Excel
- Select the Range in Excel
- Save an Excel File on Mac (Workbook)
- Print Preview in Excel
- Center Across Selection in Excel
- Active Cell in Excel
- Automatic Rolling Months in Excel
- Insert Diameter Symbol in Excel
- Insert PI Symbol (π) in Excel
- Keyboard’s Arrow Keys Aren’t Working in Excel (Scroll Lock ON-OFF)
- Open Backstage View in Excel
- Activating the Dark Mode in Excel
- Get the Scroll Bar Back in Excel
- Dependent Drop-Down List in Excel
- Dynamic Drop Down List in Excel
- Fix a Corrupt Excel File
- Highlight Top-Bottom N Values in Excel
- Insert Checkbox in Excel
- Change Page Orientation in Excel
- Default Font (Change Style, Size, and Color)
- Excel Slicer (Complete Guide)
- Highlight Alternate Rows in Excel with Color Shade
- Filter by Color in Excel
- Make Excel Default on Mac
- Open Excel in Safe Mode
- Rename a Table in Excel
- Use Icon Sets in Excel (Conditional Formatting)
- Column Numbers in Excel
- Clear Cache in Excel
- 5 Best Fonts for Microsoft Excel
- Grey Out Cells in Excel
- How to Hide Zero Values in Excel
- Make Cells Bigger in Excel
- Open a Text File in Excel (.TXT)
- Remove Dashes in Excel
- Smart Art in Excel
- Page Margin in Excel (Change and Set)
- Find Merged Cells in Excel
- Create a Custom List in Excel
- Insert PDF into Excel
- Lock Cells in Excel
- Use AutoCorrect in Excel
- Insert Picture in a Cell in Excel
- Find a Name Range in Excel
- Embed a File in Excel
- Convert PDF File into Excel
- Make Read Only in Excel
- Remove Conditional Formatting in Excel
- Turn OFF the AutoSave Feature in Excel
- Script Lab in Excel
- Extract (Get) Year or Month from a Date in Excel
- Combine Date and Time in Excel
- Round a Number to Nearest 1000 in Excel
- Add a Total Row in Excel
- Check IF a Cell Contains a Partial Text
- Combine VLOOKUP with SUMIF
- Count Filtered Rows in Excel
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- SUMIF Non-Blank (Sum Values for Non-Empty Cells)
- Use SUMIF to Sum Blank Values or Empty Cells
- Add-Subtract Percentage from a Number
- Average But Ignore Errors (#N/A and Other Errors)
- Average Number but Exclude Zeros
- Average Only Non-Blank Cells
- Business Days in a Month
- Calculate Average of the Percentage Values
- Calculate the Average of the Time Values
- Calculate the Cumulative Sum of Values (Excel Formula)
- Check IF a Cell Value is a Number
- Convert an Excel Time Value into a Decimal Number (Hours)
- Convert Date into a Text
- Convert Minutes into Hours and Minutes (HH:MM)
- Convert Time Value into Minutes (Excel Formula)
- Convert Time Value into Seconds
- Count Characters in Excel (Cell and Range)
- Countdown Days
- Create a Dynamic Hyperlink
- Find the Lowest Value from a List of Numbers (N)
- Find the Smallest Value from a Range of Cells (Smallest Number)
- Flip the First & Last Names and Add a Comma Between
- Get Current Time (Excel Formula)
- Get File Path (Excel Formula)
- Get the Value from a Cell
- Sum Time in Excel (Excel Formula)
- If a Cell Value Starts with a Text or a Number
- Median with IF (Conditional Criteria)
- Quickly Use AutoSum in Excel
- Sum Values Less Than a Particular Value (SUMIF Less Than)
- Sum Values that are Greater than Zero (SUMIF)
- Using INDIRECT with SUM
- VLOOKUP Dates (VLOOKUP for a Date from Data)
- Add Seconds to Time
- Calculate Nth Root
- Convert Month Name to Number
- Convert Seconds to Hours and Minutes
- Count Days Between Two Dates (COUNTIF Date Range)
- Extract Last Word from a Cell
- Get the Most Frequent Number(s) from an Array
- Lookup for the Cell Address Instead of the Value
- Remove Line Break from a Cell
- Remove Parentheses in Excel (Brackets)
- Round Percentage Values
- Sum Values Based on the Month (SUMIF)
- Substitute Multiple Values (Nested)
- #N/A
- Generate Random Groups
- Add Space (Single and Multiple)
- Gross Profit (Margin and Ratio)
- Calculate VAT
- Lookup Last Value from a Column or a Row
- #REF!
- Add Commas (Cell-Text)
- Compare Two Strings (Text)
- CONCATENATE IF (Combine with Condition)
- Convert a Date into a Month and Year
- Convert Time to Decimals
- Convert to Julian Date
- Count Rows (All, Blank, Non-Blank, and with Numbers)
- Get Previous Sunday
- Get the Domain from the Email ID
- Remove the Last Character from a String
- Remove Unwanted Characters
- Running Total
- Split a Text using a Space Between
- SUBTOTAL with IF (Conditional SUBTOTAL)
- Sum Values by Group
- VLOOKUP from Another Sheet (Between Sheets)
- Change Text Case (Upper, Lower, Proper)
- Extract Only Numbers from a Text (String)
- Extract Text After and Before a Character in Excel
- Greater Than (>) and Equal To (=) Operator
- How to use Arithmetic Operators in Excel
- Indirect with VLOOKUP
- Match/Compare Two Columns with VLOOKUP
- Remove Commas
- Sum of Squares
- XLOOKUP Return All Matches. Possible?
- XLOOKUP with Multiple Criteria
- TRUE and FALSE in Excel (Boolean Values)