Microsoft Excel is a popular spread-sheeting program used by many people and some on a daily basis. Below are some really helpful Excel tips around how to use excel to its full potential and become more efficient and save time.
Microsoft Excel has been around for decades, yet it remains one of the most critical tools in an IT professional’s toolkit. Whether you’re managing asset inventories, analysing logs, preparing compliance reports, exporting data from cloud platforms, or quickly manipulating CSV files from scripts and APIs, Excel is often the last mile between raw data and actionable insight.
In real-world IT environments, Excel is rarely used in isolation. It sits between systems like Azure, Microsoft 365, SIEM tools, ERP platforms, and databases. Knowing Excel well — not just the basics — can save hours per week and prevent costly mistakes.
This guide goes beyond surface-level tips. It focuses on practical Excel techniques, real-world usage patterns, and productivity enhancements that experienced Excel users rely on daily.
Working Smarter with Dates and Time
Instantly Insert Today’s Date or Time
Excel provides built-in shortcuts that outperform manual entry:
- Today’s date (static):
Ctrl + ; - Current time (static):
Ctrl + Shift + : - Dynamic date:
=TODAY() - Dynamic time:
=NOW()
Expert insight:
Static dates are ideal for audit logs and compliance documentation. Dynamic dates are better for dashboards and rolling reports — but be careful, they update every time the workbook recalculates.
Formula Efficiency and Calculation Shortcuts
AutoSum Without the Ribbon
Instead of clicking AutoSum:
- Select the cell below a column
- Press Alt + =
This inserts the SUM() formula automatically and intelligently selects the range.
Locking Cell References with $
Absolute references are essential when copying formulas:
$A$1→ Locks row and column$A1→ Locks column onlyA$1→ Locks row only
Real-world example:
When calculating tax rates, exchange rates, or SLA thresholds across large datasets, absolute references prevent silent calculation errors — one of the most common Excel mistakes in enterprise spreadsheets.
Converting Formulas to Static Values (Safely)
Sometimes formulas are no longer required — especially before sharing files externally.
Fast method:
- Highlight the formula column
- Right-click and drag the column edge
- Drag it back
- Select Copy Here as Values Only
This avoids clipboard issues and preserves formatting.
Preserving Leading Zeros (A Common IT Pain Point)
Excel loves stripping leading zeros — a nightmare for:
- Phone numbers
- Employee IDs
- Asset tags
- Postcodes
Best practice:
Paste using Paste Special → Text
Format the destination cells as Text
Creating Professional Dropdown Lists (Data Validation)
Dropdowns reduce user error and standardise input.
Referencing Another Worksheet
- Create your list on a separate sheet
- Name the range using Formulas → Define Name
- Use Data → Data Validation → List
- Set source as
=YourNamedRange
Expert tip:
Named ranges survive column shifts better than hard-coded ranges — critical for evolving spreadsheets.
Custom Ribbons for Repetitive Tasks
Excel allows custom Ribbon tabs (Excel 2010+):
- Right-click the Ribbon → Customize the Ribbon
- Create a new tab
- Add frequently used commands
Real-world use case:
IT teams that frequently clean exports (CSV imports, log formatting, Power Query refreshes) can centralise actions into one-click workflows.
Data Visualisation in Seconds
- Select your data
- Press F11
Excel instantly creates a chart on a new sheet.
For dashboards, this is often faster than navigating chart menus.
Fixing Orientation Mistakes with Transpose
Accidentally built rows instead of columns?
- Copy the data
- Paste Special → Transpose
This single feature saves hours of rework.
Inserting Screenshots for Documentation
Excel integrates directly with Windows screenshots:
- Insert → Screenshot
- Select from open applications
Perfect for runbooks, change records, and audit documentation.
Deleting Blank Rows at Scale
Blank rows break formulas, exports, and pivot tables.
Efficient cleanup:
- Enable filters
- Filter by Blanks
- Delete visible rows
- Clear filters
This is significantly safer than manual deletion.
Tracing Dependencies in Complex Spreadsheets
Inherited spreadsheets are common in IT.
- Select a cell
- Press Ctrl + Shift + ]
- Or use Formulas → Trace Dependents
This visual mapping is invaluable when debugging broken calculations.
Use $ to Prevent Shift
When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it in the next cell down, Excel will shift that referenced cell, so it would say A2 instead. To prevent shifting, use the $. So in that case, saying $A1 would prevent a shift in the column (A); A$1 prevents the shift in the row (1), and $A$1 prevents the shift change in any direction when copying a formula.
Add More Than One New Row or Column
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”
Quickly resize a column to fit contents.
You may have data in a cell that is much shorter than, or too long to fit in, the default width of a column. Instead of trying to manually adjust the width of the column to get the right size, double-click the boundary between two column headers (eg, the line between the A and the B for the first and second columns), and the column size for the column to the left will be perfectly sized to accommodate the cell with the longest text.
Track down cell dependencies
In complex worksheets, where the value in one cell impacts several other cells and formulas, it’s helpful to understand the relationship between those cells – especially if it’s a spreadsheet you didn’t personally create. This tip will help you troubleshoot and understand what’s going on.
Select a cell in your worksheet and then press Ctrl-Shift-] (that’s Control-Shift-Right Bracket) and all of the cells that are dependent on the selected cell will be highlighted.
Bonus tip: With a cell selected, click on Trace Dependencies from the Formula ribbon and you’ll see lines from the cell to the dependent cells. Click Trace Dependencies a second time and you’ll see the dependencies of those cells.
Extract characters from the left of a text string.
You may need to extract a portion of data to the left of a text string. For example, you may need to extract the area code of a phone number. Use the function LEFT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters to the far left of the text string to extract. Click OK.
Extract characters from the right of a text string.
You may need to extract a portion of data to the right of a text string. For example, you may need to extract the last four digits of a National Insurance number. Use the function RIGHT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters from the far right of the text string to extract. Click OK.
Extract characters in the middle of a text string.
You may need to extract a portion of data in the middle of a text string. For example, you may need to extract digits in the middle of a product number. Choose the function MID(Text, [Start_num], [Num_chars]). For Text, reference the cell that contains the text string. For Start_num, enter the position of the first character to extract (eg, if you wanted to start extracting at the fourth character in a text string, the Start_num would be 4). For Num_chars, enter the number of characters from the Start_num of the text string to extract. Click OK.
Merge multiple cells into one text string.
You may need to combine data from various cells into one text string in one cell. You can also include spaces, symbols, etc, in the new text string you are creating. Choose the function CONCATENATE (Text1, Text 2, etc). For Text1, enter the cell reference, text, or any other characters for the beginning of the text string. For Text2, enter the cell reference, text, or other characters for the next part of the text string, and so on. For example, if you had a first name in cell A1 and a last name in cell B1, and you wanted to combine the first and last name into one cell with a space separating the two, you would reference cell A1 for Text1, enter a space (in quotes) for Text2, and reference cell B1 for Text3: CONCATENATE (A1,” “,B1).
Change multiple cells by the same increment/ decrement
The next Tip in this list of great Excel Tips will be usre to be a big time saver. For example, to reduce a number by a percentage, take your percentage and subtract it from 100, then add a decimal in front of it. In my example, 100-2 is 98, so I’m going to be using .98 on my spreadsheet.
If you want to increase by a percentage, do 1.xx, where the xx is your percentage. For example, if you wanted to do a 2% increase, you would use 1.02.
Hers how to do it
- In an empty cell, put your number in it (.98 in my example).
- Copy that cell (Control + C).
- Highlight all of the numbers you want to change by this percentage.
- Go under your Paste menu and choose Paste Special. Choose Value and choose Multiply. After this, all of your values should be changed by your percentage.
Produce random numbers.
To produce a list of random numbers, click in the cell that will contain your first random number. Use the function RANDBETWEEN. For the Bottom, enter the lowest number in the range of random numbers (eg, if you wanted numbers between 1 and 100, 1 would be the Bottom). For Top, enter the highest number in the range of numbers (100 for the previous example). Drag that formula down (or across) as many times as needed to get the number of random numbers needed.
Remove duplicates.
A dataset can have duplicate entries, such as a customer accidentally listed twice in a CRM database. To eliminate duplicate information, select the data you want checked for duplicates and go to the Data tab, Data Tools group, and select Remove Duplicates. Select all the columns you want Excel to check for duplicates (be careful only selecting something like last name when you could have multiple customers with the same last name). Click OK.
Increase numbers by a percentage.
Excel can be used to increase numbers by a certain percentage. First, insert the amount of the percentage as a decimal added to 1. For example, a percentage increase of 20% would be entered as 1.2 in a blank cell. Copy that number. Next, select the range of cells that should be increased by the percentage. On the Home tab, Clipboard group, select Paste, then select Paste Special. Select Multiply, then click OK. Your numbers will be changed to reflect the percentage increase. See the screenshots below for an illustration. In this example, there is a list of products with their current prices. In order to change the prices to reflect a 20% increase, 1.2 was entered in a blank cell (D1). That cell was copied, and the current prices were selected (B2:B10). Under Paste Special, Multiply was selected, and the prices for each product changed to reflect a 20% increase.
Add line breaks within a cell.
If you have a lot of notes and you don’t want them all to appear in one continuous sentence; Instead each note to appear on a separate line within the cell. The problem is, if I just click Enter, my cursor will go to the cell below it.
Put your cursor in the cell where you want to put a line break.
Press Alt + Enter on your keyboard.
That’s it! Your cursor will now move down to a new line, within the cell, and you can enter your text there.
Change the case of text to capitalize the first letter of each word and all other letters to lowercase.
To change the text to capitalize the first letter of each word and make all other letters lowercase, click the cell where you would like the converted text to appear. Choose the function PROPER. For Text, select the cell that contains the text to be converted. Then copy the formula down for all text.
Disable AutoCorrect one time for one situation.
AutoCorrect can be very useful for fixing unintentional grammatical errors. However, sometimes Excel will continue “correcting” something that is not an error and should not be changed. You can instruct Excel to stop “correcting” this temporarily by typing the word, hitting the spacebar once, then clicking Undo or pressing Ctrl+Z.
Recover an unsaved workbook.
Excel will autosave your Excel workbooks (by default, every ten minutes). If you have not previously saved your workbook and then close the workbook by mistake or if your computer closes without the workbook being saved, you can recover the document. Go to File, Open, Recent Workbooks, Recover Unsaved Workbooks, and choose the file that was closed before it was saved.
Excel Keyboard Shortcuts
What would be a list of Excel Tips without showing you some Excel keyboard shortcuts. Excel, like any great software, has many excellent keyboard shortcuts. Here are some of the best.
Ctrl+; – Inserts today’s date.
Ctrl+Shift+: – Inserts the current time (the colon is what is in a clock reading, like 12:00).
Ctrl+Shift+# – Changes the format of a date.
Ctrl+5 – Applies a strikethrough to the text in a cell.
Ctrl+0 – Hides the current column.
Ctrl+9 – Hides the current row.
Ctrl+F6 – Switches between open workbooks (that is, open Excel files in different windows).
Ctrl+`– That’s the accent mark, up by the 1 key. This combo toggles the view in the sheet to show all the formulas.
Ctrl+PageUp or PageDown –Quick shift between the sheets in the currently open workbook.
Final Thoughts: Excel as a Force Multiplier
Excel isn’t just a spreadsheet — it’s a data manipulation engine. For IT professionals, mastering Excel means faster troubleshooting, cleaner reporting, better automation handoffs, and fewer mistakes.
The difference between an average Excel user and a power user isn’t talent — it’s knowing the right techniques at the right time.
If you apply even a handful of the tips above consistently, Excel will stop feeling like a chore and start acting like a genuine productivity advantage.
From my early days on the helpdesk through roles as a service desk manager, systems administrator, and network engineer, I’ve spent more than 25 years in the IT world. As I transition into cyber security, my goal is to make tech a little less confusing by sharing what I’ve learned and helping others wherever I can.