Microsoft Excel is one of the most widely used productivity applications in offices, accounting departments, businesses, schools, government organizations, and IT environments.

However, many users use only a small percentage of Excel's capabilities.

Knowing a few essential Excel formulas, functions, commands, and keyboard shortcuts can dramatically reduce repetitive work and help you manage thousands of records efficiently.

This guide provides a practical reference for everyday Excel work, including:

• Basic calculations
• Text cleaning and formatting
• Date calculations
• IF conditions
• Multiple-condition formulas
• VLOOKUP and XLOOKUP
• INDEX and MATCH
• Duplicate detection
• Data cleaning
• Sorting and filtering
• Excel Tables
• Conditional Formatting
• AMC and expiry-date management
• Business reporting
• Error handling
• Modern Microsoft 365 functions
• Keyboard shortcuts
• Troubleshooting common formula problems

1. Understanding an Excel Formula

An Excel formula normally begins with an equal sign:

=

For example:

=A1+B1

If A1 contains 100 and B1 contains 50, the result will be:

150

You can also calculate directly:

=100+50

Result:

150

Cell references are preferable because the result automatically changes when the source values change.

2. Basic Arithmetic in Excel

Excel supports the standard mathematical operators.

Operation	Operator	Example
Addition	+	=A2+B2
Subtraction	-	=A2-B2
Multiplication	*	=A2*B2
Division	/	=A2/B2
Percentage	%	=A2*18%
Exponent	^	=A2^2

Example: Calculate GST

If invoice value is in A2:

=A2*18%

If A2 contains ₹10,000, the GST amount is ₹1,800.

For total including GST:

=A2+(A2*18%)

or:

=A2*1.18

3. SUM – Add Multiple Values

One of the most commonly used Excel functions is:

=SUM(A2:A20)

It adds all numeric values from A2 through A20.

You can also add separate ranges:

=SUM(A2:A20,C2:C20)

Typical uses

SUM is useful for:

• Invoice totals
• Monthly expenses
• Sales reports
• Salary sheets
• Stock values
• GST calculations
• Outstanding balances
• AMC collections

4. AVERAGE – Calculate Average

=AVERAGE(B2:B20)

This calculates the arithmetic mean of numeric values.

For example, it can be used to calculate:

• Average monthly sales
• Average employee performance
• Average purchase price
• Average service calls
• Average customer spending

5. MIN and MAX

Find the smallest value:

=MIN(B2:B100)

Find the largest value:

=MAX(B2:B100)

These are useful for quickly identifying the lowest and highest values in a dataset.

6. COUNT, COUNTA and COUNTBLANK

Count cells containing numbers

=COUNT(A2:A100)

Count non-empty cells

=COUNTA(A2:A100)

Count empty cells

=COUNTBLANK(A2:A100)

For a customer database, for example:

=COUNTBLANK(C2:C500)

can help determine how many records have missing information in column C.

7. ROUND, ROUNDUP and ROUNDDOWN

Round a value to two decimal places:

=ROUND(A2,2)

Round upward:

=ROUNDUP(A2,0)

Round downward:

=ROUNDDOWN(A2,0)

These functions are especially useful for financial calculations, pricing, percentages, taxes, and reports.

8. ABS – Convert Negative Difference to Absolute Value

=ABS(A2-B2)

If the calculation produces -500, ABS returns:

500

This is useful when measuring differences without considering whether the difference is positive or negative.

9. UPPER – Convert Text to Capital Letters

Suppose A2 contains:

Bison Infosolutions

Use:

=UPPER(A2)

Result:

BISON INFOSOLUTIONS

10. LOWER – Convert Text to Lowercase

=LOWER(A2)

Example:

INFO@BISON.CO.IN

becomes:

info@bison.co.in

This can be useful when standardizing email addresses or imported databases.

11. PROPER – Properly Capitalize Names

=PROPER(A2)

Example:

BALVINDER SINGH

becomes:

Balvinder Singh

This is extremely useful when cleaning customer, employee, supplier, or contact databases.

Important: PROPER applies capitalization rules mechanically, so manually verify special names, abbreviations, and company names.

12. TRIM – Remove Unwanted Spaces

Imported data frequently contains unwanted spaces.

Use:

=TRIM(A2)

It removes unnecessary spaces while retaining normal single spaces between words.

For example:

Bison     Infosolutions

becomes:

Bison Infosolutions

13. CLEAN – Remove Non-Printable Characters

Data copied from websites, PDFs, ERP applications, or other systems can contain hidden/non-printable characters.

Use:

=CLEAN(A2)

A useful combination is:

=TRIM(CLEAN(A2))

This can solve many seemingly mysterious matching and lookup problems.

14. LEN – Count Characters

=LEN(A2)

If A2 contains:

BISON

the result is:

5

LEN is useful for checking:

• Mobile number length
• Employee codes
• Account numbers
• GSTIN structure
• PAN data
• Product codes
• Imported records

15. LEFT – Extract Characters from the Beginning

=LEFT(A2,5)

If A2 contains:

BISON2026

result:

BISON

16. RIGHT – Extract Characters from the End

=RIGHT(A2,4)

For:

INV-2026

result:

2026

17. MID – Extract Characters from the Middle

Syntax:

=MID(A2,start_num,num_chars)

Example:

=MID(A2,6,4)

MID is particularly useful when dealing with structured reference numbers or codes.

18. CONCAT and the & Operator

To combine first name and last name:

=A2&" "&B2

You can also use:

=CONCAT(A2," ",B2)

For example:

A2:

Balvinder

B2:

Singh

Result:

Balvinder Singh

19. TEXTJOIN – Combine Multiple Cells

=TEXTJOIN(", ",TRUE,A2:A10)

This can combine multiple values into one cell separated by commas.

It is particularly useful for:

• Email lists
• Product lists
• Keywords
• Customer lists
• Tag generation
• Export preparation

20. SUBSTITUTE – Replace Specific Text

=SUBSTITUTE(A2,"Old","New")

This replaces occurrences of Old with New.

For example:

Old Delhi

can become:

New Delhi

depending on the replacement requirement.

21. FIND and SEARCH

Find the position of text:

=FIND("@",A2)

SEARCH performs a similar operation and is not case-sensitive:

=SEARCH("@",A2)

These functions are useful when extracting usernames, domains, extensions, codes, and other structured information.

22. TODAY – Display Current Date

=TODAY()

This returns the current date according to Excel/system date handling.

It updates when the workbook recalculates.

23. NOW – Current Date and Time

=NOW()

This returns the current date and time.

Useful for:

• Reports
• Log sheets
• Monitoring workbooks
• Status dashboards

Remember that NOW is a volatile function and recalculates rather than permanently recording the original entry time.

24. Extract Day, Month and Year

If A2 contains a valid Excel date:

=DAY(A2)

=MONTH(A2)

=YEAR(A2)

These functions are useful for financial and operational reporting.

25. Calculate Days Between Two Dates

If:

• A2 = Start Date
• B2 = End Date

use:

=B2-A2

Excel returns the number of days between the dates when the result cell is formatted appropriately.

26. DATEDIF – Calculate Age or Duration

Calculate complete years:

=DATEDIF(A2,TODAY(),"Y")

Calculate complete months:

=DATEDIF(A2,TODAY(),"M")

Calculate days:

=DATEDIF(A2,TODAY(),"D")

This is useful for:

• Employee age
• Customer relationship duration
• AMC duration
• Contract duration
• Asset age

27. EDATE – Calculate Renewal or Expiry Date

If an AMC starts on the date in A2 and lasts 12 months:

=EDATE(A2,12)

This returns the date 12 months after the start date.

Very useful for:

• AMC expiry
• Subscription renewal
• Insurance renewal
• Domain expiry tracking
• Software licensing
• Contract management

28. IF – Make Decisions Automatically

Basic syntax:

=IF(condition,value_if_true,value_if_false)

Example:

=IF(A2>=50,"Pass","Fail")

If A2 is 50 or higher, Excel returns:

Pass

Otherwise:

Fail

29. IF for Payment Status

Suppose B2 contains the outstanding balance.

=IF(B2=0,"Paid","Pending")

This can automatically create a payment-status column.

30. IF for AMC Expiry

If the expiry date is stored in B2:

=IF(B2<TODAY(),"Expired","Active")

This gives a simple AMC status.

31. AND – Require Multiple Conditions

=IF(AND(A2>=50,B2>=50),"Pass","Fail")

Both conditions must be TRUE.

32. OR – Any Condition Can Be True

=IF(OR(A2="Yes",B2="Yes"),"Approved","Pending")

Only one of the conditions needs to be TRUE.

33. IFERROR – Hide Formula Errors Gracefully

Instead of showing errors such as:

#N/A
#DIV/0!
#VALUE!

use:

=IFERROR(A2/B2,0)

or:

=IFERROR(VLOOKUP(A2,D:E,2,FALSE),"Not Found")

This makes reports much easier to read.

34. COUNTIF – Count Based on One Condition

=COUNTIF(B2:B100,"Pending")

This counts the number of cells containing Pending.

Another example:

=COUNTIF(B2:B100,">10000")

counts values greater than 10,000.

35. COUNTIFS – Count Using Multiple Conditions

=COUNTIFS(B:B,"Delhi",C:C,"Active")

This counts records where:

• Column B = Delhi
• Column C = Active

36. SUMIF – Sum Based on a Condition

=SUMIF(A2:A100,"Delhi",B2:B100)

This totals values in column B only for records where column A contains Delhi.

37. SUMIFS – Sum with Multiple Conditions

=SUMIFS(C:C,A:A,"Delhi",B:B,"Active")

This is useful for advanced:

• Sales reports
• Branch reports
• Collection reports
• Customer reports
• Service reports

38. VLOOKUP – Find Data in Another Table

Classic syntax:

=VLOOKUP(A2,$F$2:$H$100,2,FALSE)

This searches the first column of the lookup table and returns a corresponding value from another column.

Typical uses include finding:

• Customer names
• Prices
• Product descriptions
• Employee information
• Account details
• AMC information

39. Why FALSE Is Important in VLOOKUP

For an exact lookup, use:

FALSE

Example:

=VLOOKUP(A2,$F$2:$H$100,3,FALSE)

Using approximate matching accidentally can return unexpected results when the lookup table is not designed for it.

40. XLOOKUP – Modern Lookup Function

Modern versions of Excel provide XLOOKUP.

Example:

=XLOOKUP(A2,F:F,G:G,"Not Found")

It searches column F and returns the corresponding result from column G.

XLOOKUP is generally easier and more flexible than traditional VLOOKUP.

However, workbook compatibility must be considered when files are shared with users running older Excel versions.

41. INDEX + MATCH

A powerful traditional alternative to VLOOKUP is:

=INDEX(G:G,MATCH(A2,F:F,0))

MATCH finds the position and INDEX returns the corresponding value.

It remains useful when maintaining workbooks designed for environments where newer lookup functions may not be available.

42. FILTER – Dynamically Return Matching Records

Modern Excel versions support:

=FILTER(A2:D100,D2:D100="Pending")

This dynamically returns rows whose status is Pending.

This can be extremely useful for creating live reports without manually applying filters.

43. UNIQUE – Create a Unique List

=UNIQUE(A2:A1000)

This returns unique values from the source range.

Useful for generating lists of:

• Customers
• Cities
• Vendors
• Products
• Email IDs
• Categories

44. SORT – Dynamically Sort Results

=SORT(A2:A100)

You can combine it with UNIQUE:

=SORT(UNIQUE(A2:A1000))

This produces a dynamically generated sorted unique list.

45. TEXTBEFORE and TEXTAFTER

Modern Excel versions can simplify text extraction.

If A2 contains:

user@example.com

use:

=TEXTBEFORE(A2,"@")

Result:

user

Use:

=TEXTAFTER(A2,"@")

Result:

example.com

This is very useful when analysing email databases.

46. TEXTSPLIT

Suppose:

Delhi,Mumbai,Kolkata

is stored in A2.

A modern Excel version can split it using:

=TEXTSPLIT(A2,",")

The individual values can spill into separate cells.

47. Remove Duplicate Records

Excel includes a built-in Remove Duplicates feature.

Typical procedure:

1. Select the dataset.
2. Open Data.
3. Select Remove Duplicates.
4. Choose the columns to compare.
5. Click OK.

Important warning

Remove Duplicates deletes duplicate records from the selected dataset.

Always make a backup or duplicate the worksheet before permanently removing important records.

48. Highlight Duplicates Without Deleting Them

For safer analysis:

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

Excel will highlight duplicates so they can be reviewed before deletion.

This is often preferable when working with accounting, customer, financial, or historical records.

49. Freeze Panes

Large worksheets become difficult to understand when column headings disappear while scrolling.

Use:

View → Freeze Panes

You can freeze:

• Top row
• First column
• Multiple rows and columns

To freeze multiple rows/columns, select the cell immediately below and to the right of the area you want to remain visible, and then select Freeze Panes.

50. Wrap Text

When long text does not fit inside a cell:

Home → Wrap Text

This allows Excel to display the content over multiple lines within the same cell.

51. AutoFit Column Width

Select the required columns and double-click the boundary between column headings.

Excel automatically adjusts the width according to the content.

You can also use the ribbon's AutoFit options.

52. Convert Data into an Excel Table

Select the data and press:

Ctrl + T

Excel Tables provide useful functionality including:

• Automatic filtering
• Structured references
• Consistent formatting
• Automatic formula propagation
• Easier expansion
• Better PivotTable sources

For regularly maintained office datasets, using an Excel Table is often preferable to maintaining an unstructured range.

53. Convert Formula Results into Static Values

Sometimes you want to remove formulas but retain their calculated results.

Procedure:

1. Select the formula cells.
2. Copy them.
3. Use Paste Special → Values.

A commonly used Windows shortcut sequence is:

Ctrl + C
Ctrl + Alt + V
V
Enter

Always verify the result before deleting the original source data.

54. Find and Replace

Find:

Ctrl + F

Replace:

Ctrl + H

This is useful for replacing:

• Company names
• Product codes
• City names
• Old email domains
• Formatting characters
• Incorrect text

Use Replace All carefully on important workbooks.

55. Go To a Specific Cell

Press:

Ctrl + G

Enter a cell reference such as:

G5000

and Excel jumps directly to that cell.

56. Essential Excel Keyboard Shortcuts

Task	Windows Shortcut
Copy	Ctrl + C
Paste	Ctrl + V
Cut	Ctrl + X
Undo	Ctrl + Z
Redo	Ctrl + Y
Save	Ctrl + S
Open	Ctrl + O
Find	Ctrl + F
Replace	Ctrl + H
Select All	Ctrl + A
Bold	Ctrl + B
Italic	Ctrl + I
Underline	Ctrl + U
Print	Ctrl + P
Go To	Ctrl + G
Insert Table	Ctrl + T
Edit active cell	F2
Insert current date	Ctrl + ;
Insert current time	Ctrl + Shift + ;
AutoSum	Alt + =
New worksheet	Shift + F11
Move to last used region	Ctrl + Arrow Key
Select to edge of data	Ctrl + Shift + Arrow Key
Format Cells	Ctrl + 1
Insert line break in cell	Alt + Enter

Note: Keyboard shortcuts can vary between Windows, macOS, Excel for the web, regional keyboard layouts, and different Excel versions.

57. Useful Formula for AMC Management

Assume:

Column	Information
A	Customer
B	AMC Start Date
C	AMC End Date
D	Status
E	Days Remaining

Status

=IF(C2<TODAY(),"Expired","Active")

Days remaining

=C2-TODAY()

A more informative version:

=IF(C2<TODAY(),"Expired",C2-TODAY()&" Days Remaining")

58. Identify AMC Expiring Within 30 Days

=IF(AND(C2>=TODAY(),C2<=TODAY()+30),"Renew Soon","")

This helps service companies identify customers who need renewal reminders.

59. Categorize AMC Status More Clearly

=IF(C2<TODAY(),"Expired",IF(C2<=TODAY()+30,"Expiring Soon","Active"))

This creates three statuses:

Expired
Expiring Soon
Active

60. Calculate Percentage

If achieved sales are in B2 and target sales are in C2:

=B2/C2

Format the result as Percentage.

For safer handling of zero targets:

=IFERROR(B2/C2,0)

61. Calculate Percentage Increase or Decrease

If old value is A2 and new value is B2:

=(B2-A2)/A2

Format the result as Percentage.

62. Extract Email Domain

For modern Excel:

=TEXTAFTER(A2,"@")

For compatibility with older Excel versions:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

If:

support@example.com

is entered, the result will be:

example.com

63. Check Whether a Cell Contains Specific Text

=IF(ISNUMBER(SEARCH("Delhi",A2)),"Found","Not Found")

Useful for analysing:

• Addresses
• Product descriptions
• Email subjects
• Remarks
• Imported transaction descriptions

64. Add Leading Zeros

Suppose A2 contains:

123

Use:

=TEXT(A2,"000000")

Result:

000123

Useful for:

• Employee IDs
• Serial numbers
• Invoice references
• Product codes

Be careful with identifiers such as account numbers: if leading zeros are significant, storing the original value as text may be safer than converting it to a number.

65. Common Excel Errors

#DIV/0!

Usually occurs when dividing by zero or a blank denominator.

Example:

=A2/B2

when B2 is zero.

Possible solution:

=IFERROR(A2/B2,0)

#N/A

Frequently occurs when a lookup cannot find a matching value.

Example solution:

=IFERROR(XLOOKUP(A2,F:F,G:G),"Not Found")

#VALUE!

Often indicates that the formula is receiving an inappropriate data type, such as text where a numeric value is expected.

Check:

• Number stored as text
• Hidden characters
• Imported data
• Spaces
• Incorrect formula arguments

#REF!

This indicates an invalid cell reference, commonly caused by deleting cells, rows, columns, or sheets referenced by a formula.

#NAME?

Usually means Excel does not recognize part of the formula.

Check for:

• Misspelled function names
• Missing quotation marks
• Incorrect named ranges
• Functions unsupported by that Excel version

 

This often does not mean the formula is broken.

A common reason is that the column is too narrow to display the formatted value.

Increase the column width and check again.

66. Numbers Stored as Text

One of the most common Excel problems is a number imported as text.

Symptoms may include:

• SUM does not behave as expected
• Sorting is incorrect
• Lookup fails
• Green triangle appears
• Numbers align differently

Possible approaches include:

• Convert to Number
• VALUE function
• Text to Columns
• Multiply by 1

Example:

=VALUE(A2)

Do not convert identifiers to numbers when their formatting, leading zeros, or long digit strings must be preserved exactly.

67. Relative vs Absolute Cell References

Normal reference:

=A2*B2

When copied downward, references change.

Absolute reference:

=A2*$F$1

$F$1 remains fixed.

Pressing F4 while editing a cell reference in desktop Excel can cycle through reference types.

Absolute references are particularly useful for:

• GST rates
• Discount percentages
• Exchange rates
• Commission rates
• Fixed multipliers

68. Sort and Filter

Excel's filtering functionality is essential for large office databases.

You can filter records based on:

• Customer
• City
• Date
• Status
• Amount
• Product
• Employee
• Department
• Expiry status

Using structured Tables makes filtering particularly convenient.

69. Conditional Formatting

Conditional Formatting can automatically highlight important records.

Examples include:

• Overdue invoices
• Expired AMC contracts
• Duplicate customer records
• Sales below target
• High-value transactions
• Negative balances

Example AMC rule:

=C2<TODAY()

You can use this condition to visually identify expired contracts.

70. Data Validation and Drop-Down Lists

Instead of manually typing status values such as:

Pending
Completed
Cancelled

create a drop-down list using:

Data → Data Validation → List

This reduces spelling variations such as:

Complete
Completed
completed
COMPLETED

Standardized values improve reporting accuracy.

71. PivotTables for Office Reporting

PivotTables can summarize large datasets without requiring complex formulas.

They can answer questions such as:

• Sales by month
• Revenue by customer
• Expenses by category
• Tickets by engineer
• AMC renewals by month
• Outstanding amount by client
• Product sales by branch

Select your structured dataset and use:

Insert → PivotTable

For regularly updated source data, consider using an Excel Table as the PivotTable source.

72. Power Query for Repetitive Data Cleaning

For recurring imports and transformations, formulas may not always be the best solution.

Power Query can help:

• Import files
• Combine multiple files
• Remove unnecessary columns
• Split columns
• Change data types
• Remove duplicates
• Filter records
• Transform imported datasets
• Refresh the process later

It is especially useful when the same cleaning operation must be repeated every day, week, or month.

73. Excel Formula vs Power Query vs PivotTable

A useful rule of thumb:

Use formulas when you need calculations directly inside worksheet cells.

Use Power Query when you repeatedly import, clean, combine, or transform data.

Use PivotTables when you need quick summarization and analysis.

In many professional workbooks, all three technologies are used together.

74. Modern Excel Functions and Version Compatibility

Functions such as:

XLOOKUP
FILTER
UNIQUE
SORT
TEXTBEFORE
TEXTAFTER
TEXTSPLIT
LET

are associated with newer Excel releases/Microsoft 365 functionality and may not be available in older installations.

Therefore, before designing a workbook for multiple employees or customers, verify which Excel versions they use.

A workbook designed exclusively around newer functions may not work correctly when opened in an older Excel installation.

75. Excel Desktop vs Excel for the Web

Excel for the web provides substantial spreadsheet functionality, but it is not identical to desktop Excel.

Differences can occur in:

• Keyboard shortcuts
• Advanced functionality
• Macros/VBA
• Add-ins
• External connections
• Certain data-management workflows

Therefore, instructions intended for desktop Excel should not automatically be assumed to work identically in a browser.

76. Excel Security – Be Careful with Macro-Enabled Files

Files ending in:

.xlsm

can contain VBA macros.

Macros can automate legitimate business operations, but malicious macros can also pose security risks.

Good practices include:

• Do not enable macros in unknown files.
• Verify the sender.
• Scan downloaded files.
• Keep Microsoft Office updated.
• Use trusted locations only where appropriate.
• Maintain endpoint security.
• Back up critical spreadsheets.
• Avoid bypassing security warnings merely to open an unknown workbook.

77. Protect Important Formulas

When creating a workbook for employees, consider protecting cells containing formulas.

This helps prevent accidental modification.

Typical workflow:

1. Identify cells users are allowed to edit.
2. Unlock those input cells.
3. Protect the worksheet.
4. Keep formula cells locked.

Remember that worksheet protection is primarily an editing-control mechanism and should not be treated as strong encryption for highly confidential information.

78. Protect the Workbook Before Major Changes

Before performing operations such as:

• Remove Duplicates
• Replace All
• Mass deletion
• Formula replacement
• Power Query transformations
• Macro execution

save a backup copy.

Example:

CustomerDatabase-BeforeCleanup.xlsx

This simple practice can prevent major data loss.

Practical Example: Customer AMC Workbook

A useful AMC sheet could contain:

Customer

Start Date

End Date

Amount

Status

Days Remaining

Renewal

Status:

=IF(C2<TODAY(),"Expired",IF(C2<=TODAY()+30,"Expiring Soon","Active"))

Days remaining:

=C2-TODAY()

Renewal attention:

=IF(AND(C2>=TODAY(),C2<=TODAY()+30),"Contact Customer","")

Using Conditional Formatting, expired and soon-to-expire contracts can then be visually highlighted.

This turns a basic Excel sheet into a useful service-management tool.

Recommended Excel Skills for Office Users

A beginner should first master:

1. Cell references
2. SUM
3. AVERAGE
4. COUNT/COUNTA
5. IF
6. COUNTIF
7. SUMIF
8. TRIM
9. LEFT/RIGHT/MID
10. Find and Replace
11. Sorting
12. Filtering
13. Tables
14. Freeze Panes
15. Conditional Formatting
16. VLOOKUP/XLOOKUP
17. PivotTables
18. Data Validation
19. Basic date formulas
20. Keyboard shortcuts

Once these are comfortable, move to:

• SUMIFS
• COUNTIFS
• INDEX/MATCH
• FILTER
• UNIQUE
• TEXT functions
• Power Query
• PivotTables
• Dynamic arrays
• LET
• VBA/macros where appropriate

Frequently Asked Questions (FAQ)

1. What are the most useful Excel formulas for office work?

Some of the most useful include SUM, AVERAGE, IF, COUNTIF, COUNTIFS, SUMIF, SUMIFS, IFERROR, VLOOKUP/XLOOKUP, TRIM, LEFT, RIGHT, TODAY and EDATE.

2. Which is better: VLOOKUP or XLOOKUP?

XLOOKUP is generally more flexible and easier to maintain in supported versions of modern Excel. VLOOKUP remains important because many older workbooks and older Excel installations use it.

3. Why does my VLOOKUP return #N/A?

Possible causes include:

• Lookup value does not exist
• Extra spaces
• Number stored as text
• Different data types
• Wrong lookup range
• Incorrect exact/approximate matching
• Hidden characters

Try cleaning the source data with TRIM/CLEAN and verifying data types.

4. How do I remove extra spaces?

Use:

=TRIM(A2)

For imported data containing non-printable characters:

=TRIM(CLEAN(A2))

5. How do I remove duplicate records?

Select the dataset and use:

Data → Remove Duplicates

Make a backup first because duplicate removal changes the dataset.

6. Can I identify duplicates without deleting them?

Yes. Use:

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

This is safer when reviewing important information.

7. How do I keep headings visible while scrolling?

Use:

View → Freeze Panes

8. How do I calculate the number of days remaining until an expiry date?

If the expiry date is C2:

=C2-TODAY()

9. How can I identify an expired AMC?

=IF(C2<TODAY(),"Expired","Active")

10. How do I identify contracts expiring within 30 days?

=IF(AND(C2>=TODAY(),C2<=TODAY()+30),"Expiring Soon","")

11. How can I combine first and last names?

=A2&" "&B2

12. How do I convert names into proper capitalization?

=PROPER(A2)

Always manually verify special names and company branding.

13. How can I extract an email domain?

Modern Excel:

=TEXTAFTER(A2,"@")

14. What does #DIV/0! mean?

The formula is attempting to divide by zero or, commonly, by an empty cell.

15. What does #REF! mean?

The formula contains an invalid reference, often because referenced cells, rows, columns, or sheets were deleted.

16. Why does Excel show #####?

Frequently, the column is simply too narrow to display the formatted value.

17. What is Ctrl + T used for?

It converts the selected range into an Excel Table.

18. What is F2 used for?

In desktop Excel, F2 edits the active cell.

19. What is Alt + = used for?

It quickly inserts AutoSum in Excel for Windows.

20. What is the difference between COUNT and COUNTA?

COUNT counts numeric cells. COUNTA counts non-empty cells.

21. What is COUNTBLANK?

It counts blank cells within a specified range.

22. What is IFERROR?

IFERROR lets you return a custom value when a calculation produces an error.

Example:

=IFERROR(A2/B2,0)

23. What is FILTER?

FILTER is a modern Excel function that dynamically returns records meeting specified conditions.

24. What does UNIQUE do?

UNIQUE dynamically returns distinct values from a range in supported Excel versions.

25. What is Power Query?

Power Query is Excel's data-import and transformation technology used for repeatable data cleaning, combining, reshaping, and refresh operations.

26. Should I use formulas or Power Query?

Use formulas for worksheet calculations and Power Query when the primary requirement is repeatable data import and transformation.

27. What is a PivotTable?

A PivotTable is an analytical tool that summarizes large datasets by categories, dates, customers, products, departments, or other fields.

28. Can Excel automatically show today's date?

Yes:

=TODAY()

29. Can Excel automatically show the current time?

Use:

=NOW()

It returns date and time and recalculates with the workbook.

30. Are Excel macros safe?

Macros from trusted sources can be useful, but unknown macro-enabled workbooks can be dangerous. Never enable macros in an untrusted file merely because the workbook requests it.

Conclusion

Microsoft Excel becomes dramatically more useful once you move beyond manually entering information into rows and columns.

Even a relatively small collection of functions such as:

SUM
IF
COUNTIF
SUMIF
IFERROR
TRIM
XLOOKUP
TODAY
EDATE
FILTER
UNIQUE

combined with Tables, Conditional Formatting, PivotTables, Data Validation, Power Query, and keyboard shortcuts can automate a large amount of everyday office work.

For business users, the objective should not simply be to memorize hundreds of formulas. Instead, understand which Excel tool solves which type of problem.

Use formulas for calculations, Tables for structured datasets, Conditional Formatting for visual alerts, PivotTables for reporting, and Power Query for repeatable data transformation.

That approach turns Excel from a basic spreadsheet into a powerful office productivity and business-data platform.

 

#MicrosoftExcel #Excel #ExcelTips #ExcelTricks #ExcelFormulas #ExcelFunctions #ExcelShortcuts #ExcelTutorial #ExcelGuide #ExcelForBeginners #ExcelForBusiness #ExcelProductivity #ExcelTraining #ExcelCommands #ExcelCheatSheet #ExcelKeyboardShortcuts #VLOOKUP #XLOOKUP #PivotTable #PowerQuery #DataCleaning #DataAnalysis #Spreadsheet #Microsoft365 #Office365 #MicrosoftOffice #ExcelIF #ExcelSUM #COUNTIF #SUMIF #IFERROR #ExcelTables #ConditionalFormatting #DataValidation #ExcelAutomation #OfficeProductivity #BusinessProductivity #ExcelReporting #ExcelDashboard #ExcelData #ExcelLookup #ExcelDates #ExcelTextFunctions #ExcelErrors #ExcelAMC #AMCMManagement #ExcelDataManagement #SpreadsheetTips #ExcelLearning #BisonKnowledgebaseel email extraction, Excel text functions, Excel financial formulas, Excel office productivity, Excel for business, advanced Excel formulas, essential Excel formulas