When you need to filter values if one of two given conditions is met or both of the conditions are met, you can do this by using OR in filters.
Just think this way, you have data for all the employees in your company and you want to filter only for people whose name is “Jon” and “John”.
Now here, the condition is to filter cells in which have the name, Jon or John. So, follow these simple steps and make sure you have sample file with you.
First of all, add filters to your data.
For the column where you want to filter data, open the filter drop down.
Now, go to -> Text Filters -> Custom Filters.
Here, you have a window to select filter options.
So, from this window, select “contains” from both of the drop downs.
And after that, in the input bar enter “Jon” and in second input bar enter “John”.
Select “Or” from the option buttons.
In the end, click OK.
Now, all the cell where you have “Jon” or “John” are filtered.
Quick Tip: Let’s say if you want to filter values based on two conditions, For Example: if you want to filter cells where you have both “Jon” and “John” in a single cell, you can use AND instead of OR.
4. Filter Top 10 Values
Get top 10 values in a few clicks.
If you have a large dataset and from that data, you want to check top 10 values then all you need a few clicks.
Here are the steps:
Apply filters first.
And after that, open the filter drop down for the column where you want to filter values.
Go to -> Number Filters – > Top 10.
This will give a pop-up window. And, from that window, you have to select following things.
Top or Bottom Values: You can select top or bottom values to filter.
Number of Values: You can also specify numbers of values you want to filter.
Type of Filter: This is a smart option where you can select the way you want to filter values.
Once you select your preferences, click OK.
So, once you click OK, all the values which are in top 10 will be filtered.
5. Using a Wildcard Character
Think out of the box.
Wildcard characters are all about partially matching and finding a text. And, you can also use wildcard characters for filtering values.
Let’s say from a name list you want to search for the names which are starting from alphabet “J”.
Here are the steps you have to follow.
Again, apply filters to your data.
And then, open filter drop down.
Now, in the search bar type “J*”.
Once you type your text, it will instantly filter all the names which are starting from the letter “J“.
After that, click OK.
You can also use this method with custom filter option as well. And, there are total three wildcard characters which you can use in Excel.
6. Pre-Defined Date Parameters
A smart way to filter dates.
You know, when it come to dates, Excel is super smart. There is already you have a lot of options to filter dates.
Just have a look at below list.
You can use any of them, they all are super useful. But, let me tell you few of them which I am sure will be a game changer for you.
Year To Date: Super useful if you want to filter all the date from the starting of the year to till date.
All Dates in a Period: It will help you to filter all the dates within a particular month or quarter.
You have total 21 + custom filter options for dates. And, if you want to filter a custom date you can use a custom filter.
7. Filter Above/Below Average Values
A handy option to check data insight.
Let’s say you want to filter values which are above average. What will you do? Add a formula? Well, you have a decent option for this.
Here are the steps:
Apply filters on the column where you have values.
After that, open filter drop- down, go to “Number Filters” and click on “Above Average”.
And, once you do that it will filter all the values which are above average.
By using the same method you can also filter values which are below average. Just click on “Below Average” instead of above average.
In a short while, you will discover that how helpful this tool is.
But, this is hard to believe that there is no listing of this data entry form in the ribbon. So, you have to make it appear before we use it.
And before you activate this form, you need a table to enter data or at least headers of the data.
To activate it, click any of the cells in the table or just select one of the heading cells and use one of the below two methods.
Use the keyboard shortcut key: Alt + D + O + O.
Add an icon to quick access toolbar.
File ➜ Options ➜ Quick Access Toolbar.
Choose Command From ➜ Commands Not In Ribbon ➜ From ➜ Add ➜ Click OK.
After using one of the above options, it will create a data entry form for you just like below.
And if you notice, all the headings of your data table automatically captured in the form.
How to Use this Data Entry Form
To use this form you need to learn some basic things which I have mentioned ahead.
#1. Create New Entry
To create a new data entry fill all the input boxes with data click on the “New” button.
Now if you look at the below example you’ll find that there is no input bar for the amount column.
No, there is no bug.
We have a formula in the amount column to multiply the quantity by the price.
That means this form is quite smart to discover the columns where there is no need to input data.
#2. Navigate Entries
To navigate all the entries which you have entered in your data, you can use the scroll bar or you can use “Find Prev” & “Find Next” buttons.
#3. Edit Entries
To edit an entry you have to first navigate to that entry by the using method mentioned above.
Once you navigate, you can make the changes in any of the input boxes and after that, just press enter key.
While editing your entry (before pressing enter or clicking close button), if you want to restore your entry you can do it with restore button.
It will restore the previous data in all the input boxes.
#4. Delete Entries
To delete an entry you have to navigate to that entry and click on the delete button.
A pop window will appear for the confirmation of the deletion and it will instantly delete that entry from your data table.
#5. Find a Specific Entry
If you are working on a large data, you can use “Criteria” button to find a specific entry.
For example, if you want to find entries from below table which match some specific criteria, just click on criteria button, enter the criteria in the input box and press Enter.
You will get the data entries matching your criteria. You can also use your criteria in more than one input box.
Important Points about this Data Entry Form
Well there some important points which you should know to play well with this form.
The maximum number of input boxes you can use in data entry form is 32.
The width of the input box is purely connected with the related column in the worksheet. If your worksheet columns are not sufficiently wide to show values, the same width will reflect in your data form also.
On the other hand, if only one of the columns is over in width, the same width will reflect for all input boxes in data form.
If you have a range with the name of "Database" then there is no need to select any cell from the table. It will always insert data in that table.
This formula is just like the formula which we have used in the second method, the only difference is here we have wrapped it in INT.
INT function returns the integer part decimal by rounding it down. You can learn more about it from here.
So when you enter this formula, first of all, it returns a decimal number for the hours and then INT removes the fractional part and gives you completed hours.
#4. Get Difference in Minutes
It happens sometimes that you need to track time difference in minutes instead of hours, and in this case, the best way is to use below formula.
In this formula, after deducting start time from end time we have multiplied it by 1440 which is the total number of minutes we have in 24 hours.
And when you enter this formula you’ll get the difference between two values in minutes.
Quick Tip: If somehow you have seconds in your time values then you use below formula to get the total complete minutes between start and end time.
#5. Get Difference in Seconds
Same like minutes we can also get the difference in seconds. All you need to do is to use following formula.
Here 86400 is the total second in 24 hours and when you enter this formula it will return the difference in start and end time in seconds.
#6. Calculate Difference as a Negative Value
In all the above example, we have the end time which is greater than (later than) the start time.
Let’s suppose you have two time values where the end time is earlier than the start time.
In simple words, if the start time is 11:00 PM at day 1, and the end time is 5:00 AM day 2.
The end time is actually less than the start time and the simple subtract formula will not work and return a negative value which will cause Excel to display a string of hash characters (i.e. ########).
Don’t worry you can use a better formula for this.
So the formula will be:
=IF(end>start, end-start, 1-start+end)
How this formula works
In this formula, you have a condition if the end time is greater than the start time then it will simply deduct end time from the start time otherwise, firstly it will deduct start time from 1 and then add it into the end time.
By subtracting the start time from 1, you will get the total time for the day 1 and when you add it to the end time, you’ll get the total time of day 1 and day 2 which is the total difference.
How to Recover Unsaved Files in Excel [Step by Step]
There are few things which I hate from my soul and a crashed Microsoft Excel is one of those things.
Just think like this, you are working on an important report and suddenly application crashed and you get a message “Excel is not responding” or “Excel has stopped working”.
Well, it’s fine if you have saved your workbook but, if not, then it’s almost like the world is going to end kind of feeling.
The fact is, you and me, we can’t avoid these kinds of things because it all depends on system’s performance.
But the good news is:
In Excel, there are some options and features which we can use to recover an unsaved file and get our work back.
Today in this post, I’d like to share with you these simple steps to recover unsaved Excel files and all the important points related to it.
...so let's get started.
Activate/Configure AutoSave Option to Recover Files
In Excel, there is a feature “AutoSave” which allows it to save files [temporary basis] even if you haven’t saved it.
To configure it,
First of all, go to File Tab and click on options.
Now in the options window, click on Save Tab.
Here you have these three options which you can configure according to your need.
Save files in this format: You can choose the format of the workbook in which you want the save your file with unsaved work.
Save AutoRecover option every: You can set the duration time in minutes to get your file autosave.
Keep the last AutoRecover: Last version if I close without saving: It creates backup versions of a file.
Important Note: Make sure to keep the duration time short to get more versions to save.
Apart from this, there is also an “AutoRecover” option which helps you to recover the last unsaved version of a file (which is saved once).
The good news is, by default these options are activated and continuously save unsaved workbooks on your system.
Recovering Unsaved Excel Files
While working with Excel for a while, I have observed that there can be three different scenarios when you need to recover your Excel files.
1. You Want Your Unsaved File Back with Data
The first case is when you are working on a file which is not yet saved anywhere just before you save it Excel got crashed or you close it without saving.
Here are the steps to recover a file which is not yet saved by you.
First of all, go to File tab and click on “Open”.
Now, click on “Recent Workbooks”.
After that, scroll to the end and click on “ Recover Unsaved Workbooks”.
Once you click on it, it shows an open dialog box, and locate it to (C:\Users\UserName\AppData\Local\Microsoft\Office\UnsavedFiles) where Excel has saved a copy of all the unsaved workbook.
Select the workbook to open and click OK.
Once you open a workbook, it shows a message alert to save that file before you use it or make any change in it.
Quick Note: The files which Excel saved as a backup is in "xlsb" format, so when you save them to make sure to use the correct format.
2. Forget to Save File or Excel Got Crashed
Now, this is the thing happen to all of us. We are working on a file which is already saved somewhere.
And, after updating is we forget to save it or Excel got crashed. In this case, everything which you have updated is lost.
With auto recover option you can recover the file with all the updated data.
Here the point is simple when you re-open Excel it shows you “Document Recovery Pane” with the list of all the unsaved version of the files.
You can click on the file which you want to open and once you open it save it.
Simple is that.
3. Get the Previous Version of a Workbook
If you use Excel 2010 or above then you can also recover the previous version of a workbook. It can be helpful if you have made a mistake in your work which can't be undone.
Here are the steps to do it:
First of all, click on "File" tab and go to the "Info" category.
Here you have a list of all the version of the workbook which you can restore.
When you click on a version, Excel opens that version along with the present one so that you can compare both.
The save AutoSave option works behind this as well. Let's say, you have specified 10 minutes in AutoSave option, Excel saves a version of your file after every 10 minutes.
3.1 Recover the Previous Version of an Excel File from OneDrive
If you are like me and love to use cloud storage to save file then you can also recover the previous version of a file. In OneDrive, there is an option to get a list of the old version of an Excel workbook.
Follow these simple steps which you can use:
Open OneDrive and then open the folder your file is saved.
After that, select that file and right click.
Now, click on the "View History" to open that file in another tab.
You just need to specify the column or row from which you want to match the value and the value to match.
MATCH (lookup_value, lookup_array, [match_type])
Let’s say you have a list of employees names and you want to check for a particular employee that where its name is in the list.
In match function, just need to enter the name in the employee and refer to the column or the row. And, it will return a number which will be the position of cell from column or row.
How INDEX and MATCH Work Together?
To understand the working of INDEX and MATCH, a simple thing you need to pick:
MATCH tells the INDEX the position (cell number) of the value in a column or in a row.
And, then the INDEX returns the value using that position (cell number).
The whole power of this formula is in these two points.
Just think like this, MATCH function is an undercover agent who finds the criminal and INDEX function is a cop who arrests that criminal afterward.
But, let’s learn this in detail how we can combine these two functions
Below is the syntax of INDEX, as you know.
INDEX(array, row_num, [column_num])
In INDEX function, row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return value from the 4th row.
To create INDEX MATCH formula we need to replace MATCH with row_argument of INDEX.
When we use MATCH, it looks up for the value from the lookup column and return the cell number of that value.
And then, INDEX use that number to figure out the position of the cell from the value column.
In the end, it returns the value from that cell and you get the value you are looking for.
But now, let’s work with a real example.
Below we have a list of cities and name of the employees who are working there.
Here we need to lookup for the employee name who’s working in Mumbai.
Now, if look at data, in the column where you have cities, that is our lookup column and the column where you have employees name, that is your value column.
And, the formula will be:
Let’s break down this formula into two parts to understand it.
Part-1: In the part one, we have used the match function to lookup for the value “Mumbai” and it has returned the “5” which is the position of the cell in which you have value “Mumbai” in cities column.
Read Full Article
Read for later
Articles marked as Favorite are saved for later viewing.
Scroll to Top
Separate tags by commas
To access this feature, please upgrade your account.