22 Limitations of Excel That Might Frustrate You

Limitation 1 – Displaying Dates in Default Format Due to Excel Limitations

When you enter 1^st Feb as 2-1 in Excel and want it to display as entered, it will display as 1-Feb instead. If you open the Format Cells dialog box and click on Date, you’ll see the required format type is not available.

To fix this problem, use the TEXT function which is more flexible than the Format Cells dialog box. The left panel in the image below shows three examples and what TEXT function will return with each of the four formats. If the number of digits is less than the expected number of digits, EXCEL will put leading zero before the actual number. For example, TEXT($C7,”M-D”) will return 5-23 while TEXT($C7,”MM-DD”) returns 05-23.

The Format Cells window is this.

The TEXT function is a good option for you to format your values. For example, you can also use the format YYYY-MM-DD to display 2015-05-23.

Read More: Advanced Excel Topics

Limitation 2 – Excel Limitations for Dropping Off Leading Zeros

If you enter social security numbers, phone numbers, credit card numbers, or postal codes into a workbook, Excel treats them as numbers and applies a general number format to them. As a result, leading zeros are removed from those number codes. If you enter 000123 into cell B5, it will display 123. Excel will display 1234 if you put 001234 into cell B6.

We can apply the TEXT function to pad a number code with leading zeros.

Another way to add leading zeros is to put a leading single quotation mark (‘) before number codes.

Add the TEXT function below in cell D5.

Press ENTER to get the output and use the Fill Handle to fill in the remaining cells.

Limitation 3 – Formatting Large Number as Scientific Notation

Any large number entered into Excel is stored as a number and abbreviated in scientific notation. It is a common Excel Limitation. If you put a long number like 12409003888123 in cell B2, Excel will apply a general number to it and the 12409003888123 will be presented in scientific notation as 1.2409E+13.

Right-click on cell B2 and select Format Cells to open the Format Cells dialog box. Select Number and set the number of decimal places as 0.

Example 4 – Using VLOOKUP Function to Approximate Match

A common Excel Limitation is that you can never be sure what VLOOKUP does with an approximate match. In the sample dataset, the lookup value is chun, and Excel returns $93,500. The correct value should be $151,200 as chun is similar to Chung.

VLOOKUP approximate match moves through the lookup table row by row and stops on the row in which the value is less than or equal to the lookup value when the value in the next row is greater than the lookup value.
In the dataset, Bueller is less than chun while Chung is greater than chun and therefore Excel stopped on the 6^th row and returned a value $93,500 in Column C. Excel stopped on 5^th row after it reached a value $71,900 which is less than $89,450 and the value in the next row is greater than $89,450. The intersection of the 5^th row and Column D gives you a commission rate of 6%.

However, the commission rate for $89,450 is %7.

We need to sort the lookup table in ascending order before using the approximate match since VLOOKUP goes down the lookup table row by row. The image below shows another lookup table in range B5:E12. This table was sorted by Sales in ascending sequence. You will find that the commission rate of $89,450 is correct.

NOTE: Sort the lookup range in ascending order before using VLOOKUP approximate match.

Limitation 5 – VLOOKUP Function Is Not Case-Sensitive

VLOOKUP is not case-sensitive and this may cause mistakes. In the image below, marie will be matched when the VLOOKUP value is Marie and thus Excel returns 14 in cell F5. What we really want is 23 in cell C6.

A combination of INDEX, MATCH, and EXACT functions can be used here to get the correct number 23. Range G5:G7 gives you a formula returning numbers in range F5:F7. The formula is,

MATCH(TRUE,INDEX(EXACT($E5,$B$4:$B$12),0),0) is the key to solving the problem. It gives you the row reference in which the exact match (Marie in our case) exists.

Another approach is to use the combination of OFFSET and MATCH functions. You can enter the following formula in the F15 cell.

You’ll get an output of 23 after pressing ENTER. Use the Fill Handle to fill in the remining cells.

Limitation 6 – Cannot Start from an Arbitrary Column or Row with VLOOKUP Function

We can only perform a left-to-right lookup with the VLOOKUP function. The OFFSET/MATCH function can enable you to start the search from any column or row. In the following example, the value in cell C10 will change as you change the values in cells C9 and B10.

Limitation 7 – VLOOKUP Function Does Not Adjust Column Offset While Deleting Columns

Suppose we have a lookup range that starts from C5 through E12. And we do VLOOKUP approximate match in range G4:I6. To retrieve the approximate commission rate for chun, enter the following formula.

After removing column C, the formula above will be replaced with the following formula.

The lookup table is changed. The cell reference for lookup value is also changed from “G5” to “F5”. But the column offset is still 3. It should be 2 as the commission rate is in the second column of the lookup table.

Limitation 8 – Difficulty in View Cell’s Long Content

In the sample dataset below, you will see that the cell references will change after you copy the formula =COUNTIFS($C3:$C10,”=”&$F3) from cell G3 to cell G4. The formula is =COUNTIFS($C4:$C11,”=”&$F4) in cell G4 and this formula returns 4. But if you look at range B2:D10, you will find that the number of male students should be 5 instead of 4. This is incorrect. The reason is that you apply relative reference – $C3:$C10 and it can change after you copy it to another cell. The correct way is to apply absolute reference and replace “$C3:$C10” with “$C4:$C11”. Similar to what we did in range F7:H9.

Limitation 9 – Not Easy to View Whole Content If Cell’s Content Is Too Long

In order to illustrate this issue, we’ve entered a paragraph into cell B6. The image below shows you that there is only one line in the worksheet and it is difficult to view the whole content.

Press ALT + ENTER to add a line break inside cell B6. There are 11 lines now.

Limitation 10 – Unable to Work Seamlessly with Microsoft Word

Excel splits one row containing a carriage return into multiple rows when copying a table from Word into Excel. We have a doc file – Copy table from word to excel.docx – containing the table below.

If we copy this table into a worksheet using CTRL+C and CTRL+V, you can see that the Last Date for Receiving of filled in Application Form is split into two rows. This is not what we want.

To prevent this from happening, we can use VBA macro to extract tables from Word files into Excel files. The code in the following table can retrieve data from all the tables within a word document into an Excel file.

Sub Import_Table()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer   'table number in Word
Dim iRow As Long    'row index in Excel
Dim iCol As Integer    'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
On Error Resume Next
ActiveSheet.Range("A:AZ").ClearContents
wdFileName = Application.GetOpenFilename("Word files (*.docx),*.docx", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user canceled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 1
For tableStart = 1 To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
ThisWorkbook.Worksheets("Import_Table").Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub

Click on the Import table button to get a table similar to that in Figure 10.2. We changed the column width here manually to demonstrate that “Last Date for Receiving of filled in Application Form” was not split into two rows.

Limitation 11 – Taking a Long Time Sometimes While Recalculation of Formulas

Excel recalculates your workbook’s formulas when you open the workbook or when you make significant changes. This feature is good but these recalculations can take a long time.

♦ Turn Off Automatic Recalculation

Click on the File tab -> Options.

Click the Formulas category.

You will get an Excel Options dialog box as shown in the image below. Automatic under Workbook Calculation in the Calculation options section means that Excel will recalculate all dependent formulas every time you make a change to a value, formula or name. Automatic except for data tables means that Excel will recalculate all dependent formulas except for data tables. The last choice Manual will enable you to turn off automatic recalculation.

The image below presents another method to turn off automatic recalculation. There are three options for you to choose.

• Automatic,
• Automatic Except for Data Tables, and
• Manual.

After you turn off automatic recalculation, you can click the Calculate Now button in the Calculation group on the Formulas tab. This method will enable Excel to recalculate all open worksheets. Another option – Calculate Sheet button – will make Excel recalculate active worksheets and any charts/chart sheets linked to this worksheet.

♦ Change the Number of Times Excel Iterates a Formula

Make sure Enable iterative calculation check box is selected before you set maximum iterations and maximum change. The higher the number of iterations, the more time Excel will need to recalculate a worksheet. The smaller the maximum amount of change, the more accurate the result and the more time Excel needs to recalculate a worksheet.

Limitation 12 – Limited Ways to Rename Multiple Excel Worksheets

When inserting multiple worksheets, using the Rename command to rename all the worksheets will take a lot of time.

To fix this problem, use the VBA code.

Here is the code for inserting and renaming multiple worksheets.

Sub rename_tab()
Application.DisplayAlerts = False
'Define variable
Dim wbk As Workbook
Dim ws As Worksheet
'Open workbookFile
Set fnm = ThisWorkbook.Sheets(1).Cells(1, 2)
Set wbk = Workbooks.Open(fnm)
'Set workbook as active workbook
wbk.Activate
For i = 2 To ThisWorkbook.Worksheets(2).UsedRange.Rows.Count
'Return number of all worksheet in the active workbook
n = Worksheets.Count
'Add worksheet after the last worksheet
Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))
'Give the newly added worksheet a name
ws.Name = ThisWorkbook.Worksheets(2).Cells(i, 2)
Next i
'Close and save active workbook
wbk.Close Savechanges:=True
End Sub

The image shows you how to design and use this macro. Suppose the macro was saved in Insert and Rename Multiple Tabs.xlsm files. There are two tabs in this xlsm file. The first one is to include a file in which you want to insert tabs. You need to enter the file pathname into cell B1. The macro will open the file listed in cell B1 (WS.xlsx in our case). The second worksheet (right panel of the figure) provides tab names that will be used by VBA macro. You can add as many tab names to the second worksheet.

The image below presents the screenshots for WS.xlsx before and after running the above VBA code.

Limitation 13 – Limitations in Excel PivotTables That Do Not Have Median

A PivotTable is one of the most powerful features. It allows you to summarize, analyze, explore and present your data. It helps you extract significant insight and detailed data sets.

The left panel in the image below contains data to be analyzed.

Click any cell in the range B5:F23. In the Insert tab, click PivotTable.

Choose the default location in the prompted Create PivotTable dialog box. You can see that this range is selected in the middle panel of the figure.

In the PivotTable field list dialog box (right panel of the figure below), drag SEX and AGE to the Row Labels area. Drag WEIGHT to the Values area. A PivotTable including cells from H5 through I19 is created.

From the pivot table, the sum of weight for male students who are 11 is 57.5. And the sum of weight for female students who are 13 is 121.8.

We can also count the number of students who are 13 or compute the average weight. Right-click on any cell in the pivot table (range H5:I19) and choose Value Field Settings. In the Value Field Settings dialog box (right panel of the figure), you can select the type of calculation you want.

The type of calculation includes Count, Average, Max, Min, etc.  If you read through all types of calculations, you will notice that EXCEL does not provide the Median statistics in the PivotTable.

To calculate Median statistics, using a MEDIAN function is a good option.

Before using the MEDIAN function, sort data by SEX and AGE.

Enter the MEDIAN function in cell J5 to calculate the Median for each subgroup.

E27:E35 refers to the WEIGHT of the female students.

Press ENTER and it returns 62.5. It displays that the Median WEIGHT for female students whose age is between 11 and 15 is 62.5 kg.

Enter the following formula for male students.

E14:E23 refers to the WEIGHT of male students.

Press ENTER to get the output as 64.15.

Limitation 14 – Excel PivotTables Limitations for Not Counting Unique Values

Sometimes, we may want to find out how many unique values exist in a range that contains duplicate values. But PivotTables do not count unique values. The image below illustrates this. You can find that there are 2 students who are 11 years old and 5 students who are 12 years old. But if you want to know the range of age distribution, you have to count them manually.

Create a PivotTable. Suppose the PivotTable is with Row Levels and Sum of AGE.

Use the ROWS function which can return the number of rows in the reference range.

H5:H10 refers to the Row Levels.

Press ENTER to get the output as 6.

The image below shows you how to count unique values for different sex groups. Apply the ROWS function in cell D5 to each subgroup for the female group.

Press ENTER to get the count as 5.

And for the male group, enter the formula into the D11 cell.

It will return 6.

Limitation 15 – No SUMIF / COUNTIF / AVERAGEIF Equivalents for Functions Such as Max Or Median

The SUMIFS function can sum cells meeting multiple criteria. The SUMIF function can only apply one criterion while the SUMIFS function can be applied to more than one set of criteria with more than one range.

The Sum_range argument in the function means the range to be summed. Criteria_range and criteria are provided in pairs.
The formula for the I5 cell is.

The sum range for cell I5 is E5:E23. The first criterion is C5:C23,”= F” while the second criterion is E5:D23,= 11. The sum of weights for female students who are 11 years old is 51.3 kg.

The AVERAGEIFS function has a similar syntax to SUMIFS. The image below shows you how to use the AVERAGEIFS function.

The COUNTIFS function is slightly different from the SUMIFS function and AVERAGEIFs function.
The syntax only consists of different pairs of criteria_range and criteria. The range like sum_range or average_range should be removed. The image below shows you how to apply the COUNTIFS function.

Limitation 16 – Features Available in Excel for Windows When Limited for Mac

Some useful reporting features do not work in Excel for Mac and so you cannot use them when generating reports for clients who use Mac. Besides these reporting features, there are also other features that are incorporated into the Windows version of Excel but are not included in the Mac version of Excel.
Excel for Windows allows you to set a default location for saving files. It can automatically save draft copies of your workbook as you work to minimize your loss if Excel crashes suddenly. Windows version of Excel can also allow you to customize the Quick Access Toolbar.

With Excel for Windows, you can preview the workbook in three modes.

• Normal
• Page Layout
• Page Break.

With Excel for Mac, you can only use Normal and Page Layout preview mode. As for the Print Preview, you can see a large print preview of the workbook and zoom in or out in the windows version of Excel. But for Excel on Mac, you can only see a small Print Preview which cannot be zoomed. The View Side by Side feature which allows you to compare two workbooks easily is only available in the Windows version of Excel. And the Synchronous Scrolling that allows you to scroll through two workbooks at the same time is also missing in Excel for Mac.

Limitation 17 – Cannot Quick Access to Tables in Excel Options Dialog Box

If you open the Excel Options dialog box, you will find that there are 10 tabs and each of them is filled with dozens of settings. Locating a specific tab can waste considerable time.

Limitation 18 – Specification Limits of Excel

Whether a workbook can be opened will be limited by available memory and system resources. The maximum number of rows is 1,048,576 and the maximum number of columns is 16,384.
The maximum number precision is 15. It means that the number of digits a cell can contain is only 15. Excel will replace digits (after the 15^th digit) with zeros. If you enter 12345678901234567890 into a cell, you will get 12345678901234500000 instead.

Limitation 19 – Limitations in Spell Checking in Excel

Microsoft Word offers automatic spell checking which can check the spelling and grammar of your files. It can highlight the mistakes. However, Excel does not automatically highlight the mistake. But, you can check for spelling errors in your Excel files by using the Reviews > Proofing > Spelling command.

Limitation 20 – Excel Crushes Easily When Running Macros

Sometimes, Excel crashes when you are running the VBA macro, especially when you use the DO WHILE loop or FOR NEXT loop which has to loop a lot of times (like 1000 or 10,000 times). Add the statement “ThisWorkbook.Save” at the end of each loop, to save the workbook.

Limitation 21 – CSV File Related Excel Issue

Excel has CSV file format issues. Sometimes, users using Excel 2013 and 2016 with language variation try to open a CSV file generated from a US application by double clicking on it. But Excel doesn’t format the file and shows it in CSV mode (comma separate) by default. Excel employs the List Separator character (such as a comma, semicolon, etc.) for regions. The following solutions may work:

• Select a region that uses a comma as a list separator (i.e. USA, Canada, etc.)
• Open and Create the CSV file in Excel and save it as a worksheet.
• Return the regional settings back to the original state.

Limitation 22 – Excel Limitations for Data Analytics

While doing data analysis in Excel, you will face difficulties because of these issues.

• Excel limits visibility when creating complex models.
• Excel limitations make collaboration more challenging.
• Excel is inefficient in managing templates and data entry.
• Limitations of Excel make keeping track of multiple spreadsheets challenging.

Read More: Basic Terminologies of Microsoft Excel

Download Practice Workbook