Unique, Filter and other functions are not available in Excel 2021

Question

Unique, Filter and other functions are not available in Excel 2021

Manu Miglani 21

I am using Excel 2021 as part of Office LTSC 2021 but I cannot find functions like Unique, Filter and other functions. I tried repairing as well as uninstalling using Microsoft Support and Recovery Assistant and again reinstalling but without success.
My Excel version is 2108 (Build 14332.21076). But I cannot use any of these functions. Please help in getting the issue resolved.

rifa abdenour 0 Reputation points

2024-02-07T15:01:08.0566667+00:00

Hello dear friend Hello my dear friend, is it possible for you to give me a method on how to find these functions in Excel? Thank you Textsplit, BYCOL, MAP, WRAPROWS, TEXTAFTER, TOCOL

4 answers

Your answer

rifa abdenour 0 Reputation points

2024-02-07T15:01:08.0566667+00:00

Hello dear friend Hello my dear friend, is it possible for you to give me a method on how to find these functions in Excel? Thank you Textsplit, BYCOL, MAP, WRAPROWS, TEXTAFTER, TOCOL

Answer 1

Manu Miglani 21

Repaired user profile and now it is working fine.

Emily Hua-MSFT 27,731 Reputation points

2022-02-11T05:31:45.917+00:00

Hi @Manu Miglani
I am glad that your issue went away.
Could you please share us more about the repairing for user profile.
This action could help others who have the same issue.
Thanks for your understanding.
syah safyan 1 Reputation point

2022-02-19T23:56:00.167+00:00

Hi Manu, I just read your situation about no filter and unique function available in office 2021.
Now, I am in same situation . I just installed office 2021 and no see that function also.
May I know how you repaired user account to solved it?
Thanks for your help.
Emily Wilson 6 Reputation points

2022-06-02T20:02:06.86+00:00

I'm interested in how you repaired your user profile as well because I seem to be having the same issue. Could you please share?
ERLING GOICO 0 Reputation points

2023-01-13T15:39:11.2+00:00

I'm interested in how you repaired your user profile as well because I seem to be having the same issue. Could you please share?
Ayomide Olajide 0 Reputation points

2023-08-13T10:15:22.8166667+00:00

Hello, can you elaborate on how you repaired the user profile?

Answer 2

Ezio De Pazzi 126

Well, I don’t have evidence, but I’m thinking your Office LTSC 2021 is not build-in with fresh, you probably uninstalled an earlier version of Office that doesn’t have those functions such as Office 2019 or 2016, and then installed Office LTSC 2021 and functions looks still the same as that earlier version of Office.
Cause frankly I used to facing this issue once, and my solutions are:

1. Check your licenses.
Even if your office has been activated doesn’t mean your licenses are nice and clear. It’s possible that some licenses for earlier version of Office are still installed on your device and it should be clear.

Try use

cscript ospp.vbs /dstatus

to locate them, then

cscript ospp.vbs /unpkey:XXXXX

2. Make sure the earlier version of Office has been FULLY UNINSTALL
This is difficult, especially when your earlier version of Office wasn’t come from Microsoft official site. I recommend to use Office Tool Plus to fully uninstall, you can clear all licenses by one-click if you want, but be sure to back up your activation-key first.

After that, reboot your device and try install Office LTSC 2021 again.

Emily Hua-MSFT 27,731 Reputation points

2022-02-07T08:53:45.76+00:00

Hi @Manu Miglani
I suggest you refer to PowerDekor-2055's suggestions, check whether there is any other versions of Office cause this issue.
Manu Miglani 21 Reputation points

2022-02-08T04:01:29.39+00:00

I have looked at the
cscript ospp.vbs /dstatus

and it is showing last 5 characters of installed product key and single key is there.

I also updated windows 10 to latest version of Windows 10. Not able to upgrade to windows 11 as some of the software don't work on windows 11.

Any more suggestions
Ezio De Pazzi 126 Reputation points

2022-02-08T07:50:08.133+00:00

if it's only single block refers to your Office LTSC 2021 then i think it's good.
try the 2nd point that i mentioned about. aka. Make sure there are no other version of Office interferring and causing this issue.
Manu Miglani 21 Reputation points

2022-02-08T08:10:51.807+00:00

I did not use Office Tool Plus as it is a third party tool but as mentioned above I did use Microsoft Support and Recovery Assistant tool to completely remove all previous versions of MS office. It is worth to mention that MS Office LTSC does not install if previous version is not fully uninstalled.
Emily Hua-MSFT 27,731 Reputation points

2022-02-08T09:31:54.24+00:00

Hi @Manu Miglani

Please check whether you can find "Xlookup" function.

For better analysis, I would suggest you create a new local Windows account, then please log into the new administrator account to check this issue.
Ezio De Pazzi 126 Reputation points

2022-02-08T10:50:46.697+00:00

Microsoft support and recovery assistant (SaRA) looks like basically a collection of SetupProd Tool. I haven't use Sara before, but SetupProd doesn't work in this issue to me at that time.

To my experience, office suit is hard to uninstall completely all the time. It's not only just about deleting files. Since office is deeply integrated with windows, there are lots of registry, dll files, services, controllers, activex will be deploy when the installing progress. And just because you can run a office 2021 apps successfully doesn't mean your earlier version of office is removed and cleared. You might checked the folders doesn't exists, but not like Linux, windows is not an “Everthing is a file” system, so you don't know about if every DLL files being re-deployed and re-resgistered or not, also services running properly, registries with no residual and all refreshed.

And yes, office 2021 cannot besides with office 2016 nor 2019 but that “besides” means you cannot install 2021 without uninstall earlier version, doesn't guarentee
proper and cleared. There is a lot of possibility that user has uninstalled earlier version and 2021 overlapped and replaced something that I mentioned above.

So in summary, if you truly believe there is nothing about that earlier version, you can try others methods but based on my experience, OTP uninstalling worked that time and frankly I don't think it's a malware.
Manu Miglani 21 Reputation points

2022-02-09T03:10:11.417+00:00

Xlookup is available. Will update after a new windows account is created.
Manu Miglani 21 Reputation points

2022-02-10T03:19:30.373+00:00

Used Office tool plus and no previous version detected.
Manu Miglani 21 Reputation points

2022-02-10T03:20:25.007+00:00

In New windows account, all functions are available.
Emily Hua-MSFT 27,731 Reputation points

2022-02-10T08:15:57.973+00:00

Hi @Manu Miglani
Thanks for your sharing.
If this issue does not occur with Excel under a new Windows account, could you please tell us did you sign any account into Office 2021? If yes, does this account also added into Windows Account? Such as "Access Work or School" and "Emails & accounts".
This does not rule out the interference of third-party software. If you can, it is recommended to perform a clean boot.

If the steps do not work, there may be something wrong with the user profile. I suggest you use this new account.

Answer 3

Emily Hua-MSFT 27,731

Hi @Manu Miglani

According to your description, will you find these 2 formulas after you enter "=UNIQUE" and "=FILTER" in cells, like folloing images?

I wouls also suggest you go to Formulas tab > Insert funtion, check whether you can find these 2 functions.

Currently, it's recommended to run Excel in safe mode to rule out the effects of add-ins.
Please hold on Ctrl key, open Excel, untile it asks you if start Excel in safe mode, then release Ctrl key and click "Yes".
And then check the formulas.
If this issue does not occur with safe mode, I suggest you go to File > Options > Add-ins > Manage, COM add-ins, Go > Clear the check boxes for any enabled COM add-ins, select OK.

Besides, it's better to update Office to the latest one for a test, at current time, the latest one is Version 2108 (Build 14332.20216).

Any updates, you can post back.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Manu Miglani 21 Reputation points

2022-02-03T15:05:16.127+00:00

I ran excel in safe mode and it showed the functions but there are no add-ins which are active (both Com Addins and excel addins).

Could you please suggest, what I can do more. I am on latest excel Version 2108 (Build 14332.20216)
Emily Hua-MSFT 27,731 Reputation points

2022-02-04T08:42:10.187+00:00

@Manu Miglani
Thanks for your quick reply.

If there is no add-ins enabled, please locate to following folders, check whether there are any files or subfolders.
C:\Program Files\Microsoft Office\root\Office16\XLStart
%AppData%\Microsoft\Excel\XLSTART
If yes, please remove them temporarily from the startup folders for a test.

If the steps above do not work, I would suggest you remove or uninstall the 3rd-party addins to check this issue.
Such as the addins related to BR&E ProMax Excel Ribbon Bar COM Addin Class, Prodes2.
rifa abdenour 0 Reputation points

2024-02-07T15:02:10.2333333+00:00

Hello my dear friend, can you help me find these functions in Excel? Textsplit ,BYCOL , MAP , WRAPROWS , TEXTAFTER , TOCOL

Answer 4

Manu Miglani 21

There are no files or subfolder in C:\Program Files\Microsoft Office\root\Office16\XLStart
or even in XLSTART folder under %AppData%\Microsoft\Excel\ in any of the user / admin / default accounts on the machine.

Removed the add-ins as advised with no success

Share via

Unique, Filter and other functions are not available in Excel 2021

4 answers

Your answer