Written by Allen Wyatt (last updated July 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Simon has a worksheet that includes date columns. He wants to search for a specific date, say 25 January 2021, formatted as 25/01/21. Simon doesn't know how to set up the Find and Replace dialog box to find it or even if it is possible.
Searching for dates is not as simple as Excel users think it should be. I'm one of those users; it should be easier. Period. But, it is not. Let me show you what I mean.
Let's take Simon's example—in a cell he has the date 25/01/21. This date is in the format d/mm/yy, but that doesn't mean that is what the cell is formatted as. If Simon is using a version of Windows where the regional settings use the d/m/y format, then Simon's date isn't truly formatted because when it was entered, Excel parsed the entry as a date and simply displayed it using the Short Date setting specified in Windows.
However, if Simon's system uses regional settings that rely on a m/d/y format, then the only way Simon could have the date displayed as 25/01/21 is to apply a custom format of d/mm/yy or dd/mm/yy to the cell in Excel, thereby overriding the default date display format.
Now, let's look at how all of this affects searching for things in Excel. The Find and Replace dialog box has three pertinent settings:
This obviously leads to multiple permutations of how to search when it comes to dates, and the permutations can be confusing.
Testing has shown, though, that the "look in" setting (Formula or Value) doesn't really matter in Simon's case, except in one instance which I'll get to shortly. Further, the Format setting only matters if Simon's data uses multiple differing date formats and he wants to find a date displayed using a specific format. (In that case, the format specified in the Find and Replace dialog box would need to match the exact format that Simon wants to find.)
That leaves the "what to search for" field. This typically needs to be set to exactly what is displayed in the cell that Simon wants to locate. In other words, if he searches for 25/01/21, then Excel will easily find the cell about which he first asked. However, if Simon searches for 25/01/2021, 25/1/21, or 25/1/2021, then the date 25/01/21 will not be matched; it won't be found. Further, if Simon searches for the date/time serial number that Excel uses internally—which would be 44221 for the date 25/01/21—it also won't result in the date being found.
Finally, notice that I said a moment ago that what you search for "typically needs to be set to exactly what is displayed." There is one case where this may not be true. Let's say that Simon's system uses a regional setting that displays dates in the m/d/y format and that, within Excel, Simon has applied a custom format of d/mm/yy to the cell. If Simon searches for 25/01/21, then his date will be found because the display matches what he is searching for. Plus, if he searches for 1/25/21 and the "look in" setting is set to Formulas, then Excel will also find the cell because that is how the date appears in the Formula bar—it is how Excel considers the date to "really be," which matches the regional settings.
To complicate matters just a bit more (is that possible?), if the date that Simon wants to find is in a row hidden by a filter, then Excel won't find it. However, if the date is in a row that has been hidden manually, then it will find the date.
This is fun, huh? It's why I mentioned at the beginning of this tip that searching for dates is nowhere as simple as it should be. The best rule of thumb is to search for the date exactly as it is displayed, using a "look in" setting of Values and no special format.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13930) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you need to delete information of unknown length at the beginning of your cells, there are a couple of ways you can ...
Discover MoreWant to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.
Discover MoreNeed to get a count of a particular result from a formula? You can use Find and Replace (as described in this tip), but ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-07-13 13:01:51
Ron S
I had the impression that you wanted to search across multiple tabs. To the best of my knowledge Find only works on a single sheet.
I'm assuming the sheets in the workbook all have the same structure, separating geographic regions or other simple specific data
At this point I would consider changing the structure of the workbook. Move all of the sheets into a single sheet, adding a column(s) to save the sheet names/type data. Then you can use the Find suggestions Allen provided
If you can't get rid of the separate tabs you could use PowerQuery to bring them together into a single "Find" or "Master" tab. On the master tab you can a slicer to recreate the filter of the original tab. Or, you could use a Pivot Table to recreate the original tabs.
Note: you can do the merge "manually", or use PowerQuery to automate the process.
Convert to the Excel Table format. It is a trivially simple change that enables several new functions You can use it to generate a pivot table to recreate the data that was on separate sheets.
On an Excel Table and/or Pivot Table you can add "Slicers", or "Timeline Slicers" to do the equivalent of a "find" by date or date ranges.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments