We would love to empower you and unlock the power of excel to you. This blog will share tutorials with examples on how to use Excel & its Functions Effectively.
This what your Excel Unlocked Blog Ad will look like to visitors! Of course you will want to use keywords and ad targeting to get the most out of your ad campaign! So purchase an ad space today before there all gone!
notice: Total Ad Spaces Available: (2) ad spaces remaining of (2)
Many a time, excel user ask me this question that “Hey Dhrunil! How to Reduce and Compress Excel File Size?“. My simple answer to this question is – Clean your excel data and Remove unwanted stuff from your excel file, and you are done. In this blog, we would go through some of the well-tested ... Read more Multiple Ways to Reduce Excel File Size The post Multiple Ways to Reduce Excel File Size appeared first on Excel...
Many a time, excel user ask me this question that “Hey Dhrunil! How to Reduce and Compress Excel File Size?“. My simple answer to this question is – Clean your excel data and Remove unwanted stuff from your excel file, and you are done. In this blog, we would go through some of the well-tested possible solutions to the question – “Why my excel file takes too long to open?“
The reason as to why someone would feel a need to reduce the file size of Excel as listed below:
Listed below are a few of the tried and tested quick ways to instantly reduce the excel file size considerably. So, here we go.
By default, whenever you open a new excel workbook (Ctrl + O), excel inserts three worksheets (named Sheet1, Sheet2, and Sheet3). But most often, you use only the first worksheet to perform your calculations and keep the other two as it is.
It is good practice to have a habit of deleting the unwanted and unused worksheets to compress your excel file. To achieve this,
When you are working on an excel file for a long time, it is quite possible that you may have created some temporary and short-term use worksheets. And more often you then finally hide these worksheets. The reason may be like you want to use these at some future date. These unwanted and hidden worksheets keep on adding to the excel file size.
To remove these hidden worksheets, firstly make them visible and then delete.
Like worksheets, many times you may hide the rows or columns that you do not need, and as a result, unknowingly this increases the excel file size.
In that case, unhide the hidden rows/columns and then delete the unwanted ones.
Many a time, the excel file may have some formatting in an empty cell (having no content) that is not visible directly. For example, you may have bold cell formatting and white background in all the cells in a worksheet. But as there is no content in the cells, the formatting is not easily visible.
This unwanted cell formatting increases the file size. To remove this formatting,
Just like normal cell formatting, applying conditional formatting to the cells also increases the excel file size. To reduce the same, removing conditional formatting is an effective option available.
The ‘Clear Format’ option does not work for conditional formatting. In order to remove conditional formatting from the cells,
This is one of the biggest reasons why your excel file is slow. Having too many complex formulas in excel file takes CPU time to do calculations and makes your excel file heavy.
If you find some of the formulas as unwanted and do not those, the best way is to paste the calculated result as values.
To achieve this,
This would instantly paste the formula results as values.
However, if you do need the formulas as well and still wish to reduce the size of your excel worksheet, you can switch off the automatic formula calculation, and set it to ‘Manual’ calculation.To achieve this:
With this, whenever you make any change in the excel file, the complex formulas in it will not recalculate by itself. It will only calculate when you want it to do so. To manually recalculate the formulas, simply use the F9 function key.
Alternatively, you may also use this path – ‘Formulas’ tab > ‘Calculation’ group > ‘Calculation Options’ > ‘Calculate Now’.
The best way to reduce the file size drastically is to convert the normal file (generally .xlsx) into a binary format file (.xlsb). The normal excel workbooks are stored in XML format, whereas the binary format excels gets stored in .bin (binary) format.
Not going much into the technicality of what .xml and .bin formats are, just remember that the .xml file format is much heavier than the later.
Below are the steps to convert an excel file into binary format file –
Now, if you check the folder/location, you would see that another excel workbook with extension “.xlsb” gets saved. Check its file size and you would see the difference.
This is the method which is performed outside the excel workbook. Meaning, this method reduces the excel file size without changing any of the content and the formatting.
And that’s it. Using this technique there is a possibility to reduce the file size by around 10% to 20% without changing the original formatting. As you can see in the image below that an original file with file size as 24,445 KB has been reduced to 10,857 KB. This is nearly a 50% reduction in file size, which is not bad.
Now you can easily share the zipped file as an email attachment and the recipient can then unzip the file and get the original excel file for use.
When you insert any picture or image in an excel worksheet, the size of the image gets added to the size of the worksheet making the excel file even larger and heavier.
One of the best ways to reduce the file size having pictures in it is by compressing the images outside the excel file using online image compressor tools and then inserting them in the excel worksheet.
Another way to reduce the size of image further is by following below steps:
You would see drastic file size reduction without compromising on the quality of the image.
With this, we have covered mainly all the possible ways that you can use to compress or reduce the size of your excel file. I would happy to know if you have some more tips to compress the excel file size. Share it in the comment box below.
In our previous special Excel Unlocked blog, we had gone through some of the mouse double click tricks that everybody should know. It helps to save a lot of your time and reduces some unwanted steps to accomplish the task. In this blog, we unlock some of the mouse and keyboard combo tricks that can ... Read more ExcelUnlocked – Mouse and Keyboard Combo Tricks The post ExcelUnlocked – Mouse and Keyboard Combo Tricks appeared first on Excel...
In our previous special Excel Unlocked blog, we had gone through some of the mouse double click tricks that everybody should know. It helps to save a lot of your time and reduces some unwanted steps to accomplish the task. In this blog, we unlock some of the mouse and keyboard combo tricks that can ease your routing excel tasks.
At the end of these four tricks, I would also share with you some bonus tricks that you can play using the right mouse click. Wait for it till the last.
Generally, when you want to move or shift a row, column, or cell in excel, you would probably use any of the below two ways:
However, you can achieve the same using combination of mouse and keyboard key very quickly. Follow the below steps:
Generally, when you want to copy and paste a row, column, or cell in excel, you would probably select it, use keyboard shortcut Ctrl + C to copy, and then paste it using Ctrl + V at the destination location.
However, you can do it quickly by using the keyboard and mouse combination. Follow the undermentioned steps to unlock this technique.
Generally when you want to create a duplicate worksheet, you would use the below steps:
However, there is another shortcut trick to achieve the same:
You would notice that the duplicate worksheet name will have the suffix as (2). If you create one more duplicate worksheet, then it would be suffixed with the number (3).
To quickly add multiple rows or columns in Excel, press and hold the Ctrl Key on your keyboard and start selecting the row/column one by one (not simultaneously) at the location where you want to insert a new row/column
Once you have completed selection, simple use Ctrl + + (Ctrl + Plus) keyboard shortcut to insert the row/column. Or you can even right-click on the row/column and select option – Insert.
Let us now learn some more hidden tricks in excel that you can perform using the right-mouse clicks.
Below is the list of excel functions that you can perform quickly using the excel right-mouse click trick.
Let us now check on some of these features.
In the below sample dummy data, there are some cells that contain formulas in it. Generally, if you want to paste those as values you would probably use the Paste Special dialog box and paste those as values.
Surprisingly, you can do it more quickly by using the right-mouse click trick as described in the below steps:
As a result, excel would instantly paste the formulas as values, as demonstrated below:
Likewise, you can create a hyperlink like the way shown in the demonstration below.
With this, we have reached the end of this blog. I hope you have enjoyed and learnt exciting shortcut tricks that you can perform using excel mouse and keyboard.
In this special ExcelUnlocked blog, let us learn to play some excel tricks with mouse double clicks. At the end of this blog, I am sure that you would save a lot of your efforts in performing excel tasks by simply using the mouse double clicks. Index – Mouse Double Click Tricks Increase Worksheet Area ... Read more ExcelUnlocked – Mouse Double Clicks Tricks in Excel The post ExcelUnlocked – Mouse Double Clicks Tricks in Excel appeared first on Excel...
In this special ExcelUnlocked blog, let us learn to play some excel tricks with mouse double clicks. At the end of this blog, I am sure that you would save a lot of your efforts in performing excel tasks by simply using the mouse double clicks.
Let us now get into each of short tricks in Excel and make our excel task quicker.
There is no standard setting to increase the worksheet area in Excel. But, wait, wait, wait, you can achieve this by hiding the ribbon options. On the bottom right corner of the ribbon, you would find an upward-facing arrow. Simply click on it.
As a result, excel would hide all the excel ribbon options and would only show the ribbon tabs.
To show the ribbon options back, double-mouse click on any of the tabs, as demonstrated below-
When you are working with Microsoft Word and PowerPoint office applications, it is very easy to select the texts using a mouse.
Although you can select a word using a double mouse click in Excel, however, surprisingly, the triple-click functionality does not work in Excel.
To achieve this, select the cell containing text and as a result, excel would show the cell content in the formula bar. Finally, click at the end of the formula bar and drag the mouse to the left. This would select the entire content in the cell.
What you achieve using keyboard shortcut keys – Ctrl + Arrow Keys is that you jump to the last row or column to right, left, top, and bottom of the excel dataset.
However, you can achieve the same using the mouse double clicks. Follow the below trick.
Hover your mouse cursor to the border of the cell till mouse cursor changes to 4 sided arrows, and then simply – double left-click. As a result, the last filled cell would get selected in that row or column.
Below I have created a dummy data to explain this.
Generally, when you want to close an Excel workbook, you use the Cross Symbol (X) on the top right corner of the excel window.
However, there is another hack that you can use to close the excel window.
Simply, double left-mouse click on the extreme top-left corner of the window. If your workbook was already saved, then it would instantly close the window. However, if your workbook is not saved, it would first ask you to save it.
When you have a formula in the first row in the dataset and you want to apply the same formula to all the row below it, you can use the mouse double click trick to perform the same.
In the below image you can see that there is a simple SUM formula in cell D2. The purpose is to find the total in all the other rows.
One way is to copy the formula in cell D2 and paste it in the other cells in column D.
Another way is by using the excel fill handle tool (double click trick). To use this, simply select cell D2 (first row) and hover your mouse cursor over the bottom-right corner of the cell D2. Once the mouse cursor changes its pointer to a plus like a symbol, simply double click. Below is the demonstration of the same.
There are many more uses of fill handle tool, which you can learn by clicking here.
Generally excel users use the right mouse click options to rename the worksheet (Right Click on Worksheet tab > Rename Option).
However, you can also rename the worksheet in a one-click step. Simply take your mouse cursor on the worksheet tab and double click over there. Type the name and then press Enter.
With this, we have reached the end of this blog. Share your views, comments, and thoughts in the comment section below.
The post ExcelUnlocked – Mouse Double Clicks Tricks in Excel appeared first on Excel Unlocked.
Do you know that excel also provides with the Header and Footer Feature in Excel? In this blog, we would learn how to insert or make a header in Excel. We would also go through some of the pre-defined excel header and footer and also learn how to create our own custom header or footer ... Read more Excel Header and Footer – Add, Change, Customize The post Excel Header and Footer – Add, Change, Customize appeared first on Excel...
Do you know that excel also provides with the Header and Footer Feature in Excel? In this blog, we would learn how to insert or make a header in Excel. We would also go through some of the pre-defined excel header and footer and also learn how to create our own custom header or footer in Excel.
Excel Headers and Footers are the text or images placed on the top and bottom of each of the pages respectively. These texts/images provide some basic information about the pages or the document like the title of the document, page number, company logo, date/time, etc.
Microsoft has given many standard headers and footers which are inbuilt to excel. Additionally, you can also create your own customized ones to make a more good looking excel document and reports.
In excel, the headers and footer feature is available at two places.
Now, when you know the path for navigating to this feature, let us begin with exploring this rarely used feature.
As mentioned in the introduction of this blog, excel has provided with some pre-defined headers and footers that you can use. To insert the preset headers and footer, you can use either of the navigation paths. Let us check on both of these.
For example, I have chosen to insert the workbook or file name as headers and the current sheet’s name and the page number as the footer part. See the image below:
As soon as you press the OK button, the dialog box would exit, and excel would insert the headers and footer on the active worksheet.
Header looks like this:
Footer looks like this:
In addition to the predefined headers and footers, you can also create custom ones by using the Header and Footer Elements. These elements are available in the ‘Design‘ tab, as shown in the image below:
Procedure to use and insert these elements:
In a similar manner, you can insert the left and right parts of the header by selecting the part and then following the above procedure.
When you are on the header part and you want to move to the footer part, there are two possible ways to do so:
One interesting thing with using the custom header or footer in excel is that you can use a combination of multiple elements. Suppose, we want to show the page number in the footer in this format – Page <Current Page No.> of <Total Pages> (For Eg – Page 1 of 19). There is no element to achieve this.
However, you can use the combination of two elements – Page Number and Number of Pages to get the required text. The below demonstration is self-explanatory.
Follow Below Steps to Insert Image or Picture in Header and Footer in Excel
Click on the appropriate header or footer area and go to the ‘Design‘ Tab.
Under the ‘Header and Footer Elements’ click on the element that says – ‘Picture‘.
In the ‘Insert Picture’ dialog box that appears, search for the picture that you want to insert and click on the ‘Insert’ button.
As a result, the excel would insert the picture inside the header or footer. Once the picture is inserted in the header or footer, the ‘Format Picture‘ element gets activated.
You can resize the picture inside the header or footer, increase picture height or width. You can also, set the top, left, bottom, and right alignment of the picture using this option. Excel also provides you with the option to change the color setting (including brightness and contrast).
When you insert a header or a footer using the method(s) stated above, it only gets added to the active sheet. Header and Footer do not apply to all the worksheets in the excel workbook.
To do so, you need to first group the worksheets and then perform the above steps to add header or footer to all those grouped worksheets.
Make sure that you remember to ungroup the grouped worksheets once your task is over.
I have an exclusive blog on Grouping and Ungrouping Worksheets in Excel. To read it, click here.
There are four miscellaneous but very important checkboxes in the Header and Footer Design Tab which are explained below:
To change the font settings of the header and footer, simply select the header or footer using the mouse (keyboard shortcut selection would not work).
A small font formatting dialog box would appear on your screen. Use the font formatting options as per your need, as shown in the below demonstration.
You can show or hide the header and footer in Excel by toggling between Normal view and the Page Layout view. See the below path to toggle between the views.
Finally, let us learn multiple ways to remove the header and footer from Excel worksheet.
With this you have reached to the end of this blog. Share your thoughts, views, and comments in the comments section below.
In our previous blog, we learned about the most used feature called Filter Feature in Excel. There we learned the meaning and purpose of Filter, multiple ways to add a filter in Excel, and how to apply a filter in single or multiple columns in Excel. We also looked into using text, number, and date ... Read more Advanced Filter in Excel – A Rarely Used Filter Feature The post Advanced Filter in Excel – A Rarely Used Filter Feature appeared first on Excel...
In our previous blog, we learned about the most used feature called Filter Feature in Excel. There we learned the meaning and purpose of Filter, multiple ways to add a filter in Excel, and how to apply a filter in single or multiple columns in Excel. We also looked into using text, number, and date filter in Excel and how to filter by color. In continuation of it, in this blog, we would unlock the Advanced Filter feature in Excel.
We would be using the same sample data that we used in our previous blog. Use the Download button to download the sample file to practice along with.
As the name in itself is self-explanatory, the Advanced Filter is a higher version of the AutoFilter feature in Excel. It helps to perform more complex filtering to the data set (which regular AutoFilter option could not perform).
This feature is available since Excel 2003 version.
Below is the list of difference between the normal AutoFilter feature and the Advanced Excel Filter feature-
With this, I can now say that you are having a high-level understanding of the added advantage of advanced filter over the normal filter.
The advanced filter feature in Excel is available in the ribbon options. Follow the below navigation path for the same:
Data Tab > Sort & Filter Group > Advanced Option (as shown in the image below)
As soon as you click on the ‘Advanced’ option, the Advanced Filter dialog box would pop out on your screen.
The Advanced Filter Dialog Box has following options in it-
Once of the disadvantage of using the Advanced Filter dialog box is that the List Range and the Criteria Range only accepts cells references (and not manually entered values).
You may be aware of the ‘Remove Duplicate’ feature of Excel under the Data tab which extracts unique records from the data set. That’s true.
Then why to use the ‘Advanced Filter’ feature to remove duplicates? The answer is that – Advanced Filter feature not only removes the duplicate values in excel table but also filters the data based on specific criteria. Also, with this feature you can get the new list at some other location without changing the original data set. Let us check this out.
I have created some duplicate records in the sample data (colored as green), as shown below:
Follow the undermentioned steps to remove the duplicate records:
As soon as you click on the ‘OK’ button, copy the data set at the destination location ($G$3) and remove the duplicate rows from there. There is no change in the original data table.
Just like a normal filter feature in excel, the advanced filter can allow you to carry out more complex filtering based on some criteria.
An added advantage of this feature is that you can filter out the data table and copy the filtered data at some other location.
Let us learn advanced filtering starting from basic filtering and moving towards doing some complex one.
As mentioned earlier, the advanced filter feature does not allow manual entry in the ‘Advanced Filter’ dialog box fields. It only accepts cell reference. Therefore, the first step would be to have the table column headers somewhere in the worksheet so that we can give reference to those cells, like this-
Make sure that the table headings text in the copied area should be exactly the same as that of the original dataset.
Suppose, you wish to filter out all the records for ‘Female’ candidates or applications. Follow the under mentioned steps:
As soon as you click on the OK button, you would notice that excel filters out the female candidate list and puts it at the destination cell G5. Also, the original data set table remains intact without any change.
In a similar way, let us now perform the same with two criteria. Let us filter and copy the data set for ‘Male’ candidates who have enrolled for ‘Masters’ degree.
To do this, simply write ‘Masters’ in cell I2 and ‘Male’ in cell J2, and follow the procedure performed above. As a result, the excel would filter for all the records starting with Masters AND for Male candidates.
In this section, we would go a bit deeper and learn to use both the ‘AND‘ as well as ‘OR‘ operator while performing advanced filter.
For using the AND and OR operators, excel uses following rule:
Suppose, we want to filter out all the records for Masters or Phd programs enrolled by only Male Candidates.
Over here the ‘Criteria Range’ would be $G$1:$K$3.
You can also use the logical operators (such as greater than, less than, equal to, etc.) with the Advanced filter to filter the data set accordingly.
The logical operators work in a different manner of text data, numbers, and dates, as explained below:
|Criteria||Explanation||Example from Sample Data|
|word||Show the data that begins with the specified word||Entering Bachelors will show all the data that begins with this word|
|=word||Show the cells that exactly matches the word||Entering =”=Bachelors of Commerce” will show the cells that exactly contain this word|
|<>word||Show the cells that do not exactly match the word||Entering <>Bachelors of Commerce will show all the cells that do not exactly contain this word|
|>word||Show the data that are alphabetically after word.||Entering >Masters in Business Administration will show data that comes after this word alphabetically|
|<word||Show the data that are alphabetically before word||Entering <Masters in Business Administration will show data that comes before this word alphabetically|
The Logical operators (=,>,<,<>,>=,<=) also works when you are filtering the data set based on the date (using Advanced Filter). For example, >01-04-2019 will filter the data set which falls after 01-04-2019. In a like way, <=31-12-2019 will filter the dataset which falls on or before 31-12-2019.
However, when you want to filter data based on dates that fall between two dates, you need to enter the extreme dates (start and end date) in two different columns with same headings, as shown in the image below:
Likewise, the number filter also uses similar logical operators. For example, <>10000 on the column ‘Tuition Fee’ will show all the values other than 10000.
Excel Expert Svetlana Cheusheva has many more examples for you in this link.
Just like normal filter, excel advanced filter feature also allows you to perform non-exact match of text in Excel and apply the filter accordingly. This is done using the wildcard character in the criteria range.
Excel supports are three types of wildcard characters which are – asterisk (*), question mark (?), and tilde (~).
For example, *Arts* in the criteria range will search for the program which contains the word ‘Arts’ (irrespective of any number of words before or after it). Likewise, Bachelor? will search for text which has the word Bachelor followed by any one character like Bachelors. Car~* will search for the exact word Car*, and Rat~? will search for exact word Rat?.
The post Advanced Filter in Excel – A Rarely Used Filter Feature appeared first on Excel Unlocked.
The Excel Filter Feature is a great tool that proves to be a lifesaver at the time when you are working with huge data in Excel. In this blog, we would unlock this filter feature in Excel. We would learn how to filter huge data in excel. We would also get into answering the following ... Read more Filter in Excel – Add, Apply, Copy, Clear, and Many More The post Filter in Excel – Add, Apply, Copy, Clear, and Many More appeared first on Excel...
The Excel Filter Feature is a great tool that proves to be a lifesaver at the time when you are working with huge data in Excel. In this blog, we would unlock this filter feature in Excel. We would learn how to filter huge data in excel. We would also get into answering the following common questions – how to filter values, or numbers, or dates and time in Excel, how to use filter using wildcard characters in excel, how to filter using the search box, how to filter by text or cell color.
In this blog, we would be using the below data. Kindly download and practice along with using the Download button.
The Filter Feature (also known as AutoFilter) is a powerful tool provided by Excel that narrows down the excel data or data in excel tables to show only those data that you want to see by temporarily hiding all other data. In our sample data, for example, we can use this feature to condense the data such that it only shows the programs taken up by the ‘Female‘ applicants.
In a similar way, you can filter by dates, or values or by any other such criteria.
The filters are always added to the headers of the data set. Therefore, it is important that your excel data must contain a header row, with logical headings. In our example, row 3 is the header row with headings describing the column data.
Once your dataset has proper headers, you are now good to insert the AutoFilter on these headers. To add AutoFilter on the header row, click on any of the cells inside the excel data set (like C7) and use any of the below-mentioned ways:
As soon as you use any of the above methods, the excel would automatically detect the header row inside the table or data set and insert filter buttons on each of the header cells. These are the downward-facing arrows, like the way shown below:
Once the filter option added to the headers, you are good to start filtering your data set.
With this feature, you can filter your data by a single column or by multiple columns.
To apply the filter on a single column, simply click on the filter button (downward-facing arrow) of the respective column. (Let us firstly apply the filter on the column ‘Program Name’). As a result, you would notice that all the checkboxes are checked by default.
Uncheck the checkbox that says ‘Select All‘. This would remove the check boxes from all the values.
Now, start ticking the values that you wish to view and click on ‘OK’, like the way demonstrated in the below image.
Consequently, the excel would view or show only the selected values in the column, rest all are hidden. Don’t worry excel has not deleted them, they are still there in the backend.
In a similar way, you can apply the filter on other columns in the Excel Data Table. There is no limitation on how many columns to which you can apply the filter.
Let us now apply another filter on the column having header – ‘Gender‘.
As a result, the excel would now only display the data set rows for selected ‘Program’ and for ‘Female’ candidates. All others are temporarily hidden (not removed).
Some Useful Points and Tips
Once a filter is applied on the header row-
In the earlier section, we learned about how to filter your data set by selecting the checkboxes in the Filter window. There is yet another way to filter out a specific text or value or numbers or dates/time in Excel which is by using the ‘Filter’ search box. The Filter search box is placed just above the list of values in the Filter window.
To filter your data set using the ‘Filter’ search box, simply click on the ‘Filter’ drop-down icon on the header and type the search value in the search box. As a result, the excel would narrow down the list to show only the specified values. Finally, click on the ‘OK’ button to apply the filter.
To illustrate with an example, let us filter out the ‘Program’ column with the Bachelor’s degree.
Not sure about the exact text to search, then use wildcard characters in the search box for a non-exact filter search in Excel. If you are unaware of what are wildcard characters in Excel and its usage, then please click here.
When you want to clear filter from selected or all the header columns in Excel, you can use any of the below-mentioned ways:
When you clear the filter from the column ‘Gender’, the data set would now only have a filter on the column header ‘Program’. It has only cleared filter from the row ‘Gender’ and has not touched any other filters.
To clear multiple filter from all the headers cells, use any of the following way:
It is important to note that the above method only clears the filter from the header cell. It does not remove the filter icon from the header row. To learn how to remove the filter in Excel, wait until the end of this blog.
When you are working with text filters, there are many other text filter options available in addition to what we learned above. Below is the list of the same.
These advanced filter options are available under the ‘Text Filter’ section of the Filter Window as shown in the image below:
Let us understand this with the help of a small example: Let us, for example, get the program that contains the word ‘Business’ in it. To achieve it,
Click on the ‘Filter‘ icon on the column header – ‘Program’, and take your mouse cursor to the option – Text Filters.
In the list of options that come up, select the option that says – ‘Contains‘. As a result, the ‘Custom AutoFilter‘ dialog box would appear on your screen. In the input box besides the word ‘Contains’, write the text that you want to filter (in our case ‘Business’) and press OK.
As soon as you press OK, excel would show all the values or text that contains the word ‘Business’ and will hide all other data rows.
Note that, just like Filter search box, this ‘Custom AutoFilter’ dialog box also accepts wildcard character search.
The Custom AutoFilter dialog box also allows us to filter out the data rows based on two criteria. Use the And and Or operator radio buttons based on how you wish to filter.
For example: Let us filter out all the programs for Commerce and Science. In this case, as we want both the values, we shall use Or operator, like this-
This would filter out those values which either contain the word ‘Commerce‘ or contain the word ‘Science‘.
Similar to the text filter learned above, there are many additional filter options available carry Number Filters in Excel. Below is the list of the same:
The filter option Top 10 does not exactly only mean Top 10 values. You can change it to any other value like Top 15, or Top 27 Values, and even Bottom 5 values and so on.
Unlike the Text and Number Filter, the Dates Filters option allows us with more advanced filtering options. The below image in itself is self-explanatory.
In a nutshell, below options are available for filtering dates in Excel –
Important to notice – Excel by default groups all the dates by year, months, and then the days. You can expand or collapse the groups (year, months, and days) to filter the dates and check/uncheck it to filter accordingly.
In the above image, you can see that all the dates are grouped by years first (2020 and 2019). Within the years there are months and then the days in the month. If you clear the checkbox for 2020, then the excel would only show the dates that lie in 2019.
When the data set contains any text with color or any cell with a background color, you can use the ‘Filter by Color‘ option in the ‘Filter’ Window to filter the dataset by a specific text or cell background color.
Let me explain this with a small example. I am changing the background color of different cells to yellow, orange, and green. Also, I have the font color in a few of the cells as ‘Red’.
To filter the values based on color, simply click on the ‘Filter’ drop-down arrow button on the header cell and take your mouse cursor over the option that says – ‘Filter by Color’.
As a result, you would see that excel lists all the cell and font color. You can choose the color that you want to filter with.
Let us, for example, filter the programs by Red font color. The result of the filter would be as below.
Instead of using the ‘Filter’ drop-down icon to filter, you can even filter the dataset based on a specific cell’s value, icon, or font and cell color. To do so, follow the undermentioned steps:
As a result, excel would filter the dataset based on the cell color – Yellow, as demonstrated in the below image:
When you make any changes in the filtered data, you would notice that excel does not refresh the filter automatically. Yes, that is absolutely true. You need to reapply the filter in order to refresh the data and apply the filter on the changed data. There are a couple of ways to do the same, as listed below:
This option is also available in the Home Tab > Editing Group > Sort & Filter Option > Reapply.
There are two possibilities to copy and paste only filtered data in Excel.
Simply, click on any of the cell in the filtered area, and press Ctrl + A to select the entire excel table or data set. As a result, you would notice that excel selects the entire table including both data rows as well as the header row. Now, press Ctrl + C to copy the selection and use Ctrl + V at the destination location to paste the filtered data cells.
To copy and paste only filtered data (without header row), select the top-left cell of the data (which in our case it is C4). Press keyboard keys Ctrl + Shift + Down_Arrow and then press Ctrl + Shift + Right_Arrow (instead you can even use Ctrl + Shift + End). Any of these ways would select the entire data set (excluding headers). Now, use Ctrl + C to copy the selection and then Ctrl + V to paste the filtered data set at the destination cell.
Usually, when you copy the filtered dataset using the above methods, excel does not include the hidden rows in the copy area. The hidden rows are excluded and it only takes the visible rows while copying. However, sometimes when your data is huge, it may not work in expected behavior. In those cases, to play safe, you can use the GoTo (F5) > Special > Visible Cells Only feature to select only the visible rows. The keyboard shortcut for the same is Alt + ; (semi-colon).
Finally, let us now learn how to remove filters from the excel header row. There are multiple ways using which you can remove filters, as listed below-
The post Filter in Excel – Add, Apply, Copy, Clear, and Many More appeared first on Excel Unlocked.
Or if you prefer use one of our linkware images? Click here
If you are the owner of Excel Unlocked, or someone who enjoys this blog why not upgrade it to a Featured Listing or Permanent Listing?