Blogging Fusion Blog Directory the #1 blog directory and oldest directory online.

Excel Unlocked

Home Excel Unlocked

Excel Unlocked

Rated: 5.00 / 5 | 111 listing views Excel Unlocked Blogging Fusion Blog Directory

United-States

 

General Audience

  • Nitin
  • April 16, 2020 11:33:42 AM
SHARE THIS PAGE ON:

A Little About Us

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.

Listing Details

Listing Statistics

Add ReviewMe Button

Review Excel Unlocked at Blogging Fusion Blog Directory

Add SEO Score Button

My Blogging Fusion Score

Google Adsense™ Share Program

Alexa Web Ranking: 2,629,521

Alexa Ranking - Excel Unlocked

Example Ad for Excel Unlocked

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!

https://www.bloggingfusion.com

.

notice: Total Ad Spaces Available: (2) ad spaces remaining of (2)

Advertise Here?

  • Blog specific ad placement
  • Customize the title link
  • Place a detailed description
  • It appears here within the content
  • Approved within 24 hours!
  • 100% Satisfaction
  • Or 3 months absolutely free;
  • No questions asked!

Subscribe to Excel Unlocked

Multiple Ways to Reduce Excel File Size

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?

Why is There a Need to Compress Excel Size

The reason as to why someone would feel a need to reduce the file size of Excel as listed below:

  1. Reducing file size would save computer disk space.
  2. Reducing the excel file size would also speed up the initial loading time of the file.
  3. It is generally not possible to send large excel files as an email attachment. Reducing the file size would enable you to achieve this.
  4. It also enhances the reading and writing time of your RAM (Random Access Memory).

Quick Ways to Reduce Excel File Size

Listed below are a few of the tried and tested quick ways to instantly reduce the excel file size considerably. So, here we go.

1# Remove or Delete Extra Worksheet

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,

  • Simply right-click on the worksheet tab and click on the option – “Delete” as shown below:
Delete Worksheets

2# Remove or Delete Hidden Worksheet

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.

  • To make the hidden worksheets visible, right-click on any of the worksheet tabs and select the option – ‘Unhide’.
Unhide Worksheets Option
  • In the ‘Unhide’ dialog box that appears, select the worksheet that you want to make visible and click OK. Note that you can only unhide one worksheet at a time.
Unhide dialog Box
  • Finally delete the unwanted worksheets (Right-click > Delete).
Delete Worksheets Option

3# Delete Hidden and Unwanted Rows or Columns

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.

Data with Hidden Rows and Columns

In that case, unhide the hidden rows/columns and then delete the unwanted ones.

  • To unhide hidden rows/columns, select the hidden area, right-click on the headers and click on the option ‘Unhide’.
Unhide Rows or Columns in Excel
  • Once the rows or columns are visible, select unwanted row or column, right-click on the header and select the option – ‘Delete’.
Delete Unwanted Rows or Columns

4# Remove Formatting from the Cells

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,

  • Select all the blank and unused cells and then navigate to the ‘Home’ tab > ‘Editing’ group > ‘Clear’ option > ‘Clear Formats’. See the image below:
Clear Formats Option

5# Clear Conditional Formatting from Cells

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,

  • Firstly, select the cells and then navigate to the path – ‘Home’ tab > ‘Editing’ group > ‘Conditional Formatting’ > ‘Clear Rules’ > ‘Clear Rules from Selected Cells’.
Remove Conditional Formatting

6# Remove Unwanted Formulas from Excel Worksheet

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,

  • Select the cell(s) and use keyboard shortcut Ctrl + C to copy the cells.
  • Now, use keyboard shortcut Ctrl + Alt + V to open Paste Special dialog box. Here, select the radio button ‘Values’ and click OK, as highlighted below:
Paste Special Dialog Box

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:

  • Go to the ‘Formulas’ tab > ‘Calculation’ group > ‘Calculation Options’ > Click on ‘Manual’.
Activating Manual Formula Calculation

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’.

Reduce Excel File Size

7# Convert the Excel File (.xlsx) into Binary Format (.xlsb)

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 –

  • Go to the ‘File’ tab under the ribbon tabs, and click on the ‘Save As’ option.
File Save As Navigation
  • In the Save As dialog box that appears, select the file type as ‘Excel Binary Workbook (*.xlsb)’ and click the ‘Save’ button. See the image below:
Save As - Excel Binary Format xlsb

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.

8# ZIP Excel File to Compress It

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.

  • Simply right-click on the file and then choose the option Send To > Compressed (Zipped) Folder.
Compressing to ZIP file Navigation

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.

Zipped File Size Reduced

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.

9# Compress Images or Pictures in Excel

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:

  • Select any of the images in the excel worksheet and navigate to the following path – ‘Picture Format’ Tab > ‘Adjust’ Group > ‘Compress Pictures’ option.
Compress Pictures Navigation
  • As a result, the ‘Compress Pictures’ dialog box would appear. Uncheck the checkbox – ‘Apply only to this Picture’ and select the radio button – ‘E-mail (96 ppi): minimize document size for sharing’, as shown in the image below:
Compress Pictures Dialog Box
  • Finally, click on ‘OK’ and that’s it. Save the workbook and check the file size.

You would see drastic file size reduction without compromising on the quality of the image.

Compressed Picture Result

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.

The post Multiple Ways to Reduce Excel File Size appeared first on Excel Unlocked.


ExcelUnlocked – Mouse and Keyboard Combo Tricks

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.

Index – Mouse and Keyboard Combo Tricks

  • A quick way to Shift and Move Rows, Columns or Cells in Excel
  • Quickly copy and paste Row, Column or Cell in Excel
  • Create a Duplicate Worksheet At One Go
  • A quick way to Add Multiple Excel Rows or Columns

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.

Mouse and Keyboard Combo Tricks

Quick Way to Shift and Move Rows, Columns or Cells in Excel

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:

  1. Firstly, cut the row or column (Right Click > Cut) and then, insert it at the other location (Right Click > Insert Cut Cells), or
  2. You would firstly insert a row or column (as the case may) at the destination location, then cut and paste the original row or column at the newly inserted cells.

However, you can achieve the same using combination of mouse and keyboard key very quickly. Follow the below steps:

  • Select the row, column, or cell range that you want to move.
  • Then, press and hold the Shift key on your keyboard and take your mouse cursor on the edge of the selection.
  • The mouse cursor would change to a four arrowed pointer.
  • Finally, keeping the shift key pressed, left-mouse click and drag the mouse towards the destination location.
Move Row, Column or Cells in Excel - Mouse Keyboard Combo
Move Row, Column or Cells in Excel #2 - Mouse Keyboard Combo

Quickly Copy and Paste Row, Column or Cell in Excel

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.

  • Select the row, column, or cell range that you want to copy.
  • Then, press and hold the Shift and Ctrl key on your keyboard and take your mouse cursor on the edge of the selection.
  • The mouse cursor would change to an arrow with a small plus sign with it.
  • Finally, keeping the Shift and Ctrl key pressed, click and drag the mouse to the destination location.
Copy Row, Column or Cells in Excel - Mouse Keyboard Combo
Copy Row, Column or Cells in Excel #2 - Mouse Keyboard Combo

Create Duplicate Worksheet At Once

Generally when you want to create a duplicate worksheet, you would use the below steps:

  1. Right-click on the worksheet tab and click on the option that says – ‘Move or Copy‘.
  2. In the ‘Move or Copy‘ dialog box, click on ‘move to end’ and tick the checkbox – ‘Create a Copy‘.

However, there is another shortcut trick to achieve the same:

  • Press and hold the Ctrl key on your keyboard.
  • Finally, keeping the Ctrl key pressed, left-mouse click on the worksheet tab and drag the mouse to the location where you want to create a copy of the sheet.

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).

Create Duplicate Worksheet - Mouse Keyboard Combo

Quick way to Add Multiple Excel Rows or Columns

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.

Quickly Insert Multiple Rows At One Go - Mouse Keyboard Combo
Quickly Insert Multiple Rows At One Go #2 - Mouse Keyboard Combo

Let us now learn some more hidden tricks in excel that you can perform using the right-mouse clicks.

Mouse and Keyboard Combo Tricks

Hidden Excel Right-Mouse Click Tricks

Below is the list of excel functions that you can perform quickly using the excel right-mouse click trick.

  1. Move or copy the cell or range of cells as it is without using excel cut, copy, paste function.
  2. Copy the cell content as Values or Copy only cell format
  3. Create a link or hyperlink quickly
  4. Shift or move the cell or range of cell down or right

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.

Sample Data - Right Mouse Click Feature

Surprisingly, you can do it more quickly by using the right-mouse click trick as described in the below steps:

  • Firstly, select the cell or the range of cells that contains the formula (in our case let us do it on D1:D7).
  • Now, take your mouse cursor over the selection edge till your mouse cursor changes to a four-sided arrow.
  • Then, press and hold the right-mouse click (do not release), and drag the mouse at the location where you want to paste them as value.
  • Once you are at the destination location, release the right-mouse click and select the option that says – ‘Copy here as Values Only’.

As a result, excel would instantly paste the formulas as values, as demonstrated below:

Paste As Values - Right Mouse Click Feature

Likewise, you can create a hyperlink like the way shown in the demonstration below.

Create Hyperlink - Right Mouse Click Feature

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.

The post ExcelUnlocked – Mouse and Keyboard Combo Tricks appeared first on Excel Unlocked.


ExcelUnlocked – Mouse Double Clicks Tricks in 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. 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.

Index – Mouse Double Click Tricks

  • Increase Worksheet Area in Excel – Show & Hide Ribbon Options
  • Quickly Edit and Copy the Cell Content
  • Quickly go or move to the last row/column in Excel Table
  • The fastest way to close the Excel workbook window
  • Using Excel Fill Handle tool to Fill Down
  • Rename Excel Worksheet Using Mouse Double Click

Let us now get into each of short tricks in Excel and make our excel task quicker.

Mouse Double Click Excel Tricks

Increase Worksheet Area in Excel

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.

Hide Ribbon - Mouse Click Tricks

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-

Show Ribbon Options - Mouse Click Tricks

Quickly Edit and copy the Cell Content

When you are working with Microsoft Word and PowerPoint office applications, it is very easy to select the texts using a mouse.

  • To select only a particular word, simply double click on the word.
  • To select an entire sentence, triple-click on any of the words.

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.

Copy Cell Content in Excel - Mouse Click Tricks

Quickly Move to Last Row or Column in Excel Data

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.

Jump to the Last Row or Column - Mouse Click Tricks

Fastest Way to Close Excel Window

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.

Close Window - Mouse Click Tricks
Mouse Double Click Tricks

Double Click to Fill Down (Using Fill Handle Tool)

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.

Total Formula in Row 1

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.

Fill Handle Tool Demonstration - Mouse Click Tricks

There are many more uses of fill handle tool, which you can learn by clicking here.

Rename Excel Worksheet Name Using Mouse Double Click

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.

Rename Worksheet - Mouse Click Tricks

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.


Excel Header and Footer – Add, Change, Customize

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.

Brief Information on Excel Headers and Footers

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.

Navigating to Header and Footer Feature in Excel

In excel, the headers and footer feature is available at two places.

  • Go to the ribbon tab Page Layout. Under the Page Setup group, you would find a small icon on the bottom right corner. This is the dialog box launcher. Click on it and the Page Setup dialog box would appear on the screen. There, you can find a specific tab named – Header/Footer.
Headers and Footers Navigation Path #1
  • Additionally, you would find the Excel headers and footers feature under the ‘Insert‘ Tab. Click on the ‘Insert‘ tab > ‘Text‘ group > Header & Footer Option.
Headers and Footers Navigation Path #2

Now, when you know the path for navigating to this feature, let us begin with exploring this rarely used feature.

How to Insert Excel Preset Header and Footer

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.

Using Page Setup Dialog Box

  • Navigate to the Page Setup dialog box > Header/Footer tab (as learned in previous section).
  • The option ‘None‘ (shown in the screenshot below) means at present no headers/footers are put in the worksheet.
No Header Footer Setting
  • Click on the ‘Header‘ drop-down option arrow and there you would find many available header options provided by Microsoft. Select one of your choices. Similarly, under the ‘Footer‘ drop-down option, you can find and select the one from the available default footers.

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:

Excel PreSet Headers Footers Example

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:

Headers on Sheet

Footer looks like this:

Footers on Sheet

Using Insert Header and Footer – Design Tab

  • Go to the Insert tab and click on the option – Header & Footer (under Text group).
  • This would instantly and quickly create a structure of excel headers and footers in the worksheet. Simultaneously, excel also inserts a new tab in the ribbon with the name ‘Design‘ (as shown below).
Header and Footer structure and Design Tab
  • To insert preset excel headers, click on the header structure in the worksheet area. Under the Design tab, click on the option that says ‘Header‘. As a result, a drop-down list of all the available predefined excel headers would be displayed. Select one of your choices and that’s it.
Inserting Header Using Design Tab
  • Similarly, to insert footer from the predefined list of footers, click on the option that says ‘Footer‘ under the ‘Design’ Tab > ‘Header & Footer’ group. From the list of drop-down options, select one of your choices, and you are done.

Custom Header and Footer Using Elements

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:

Header and Footer Elements

Procedure to use and insert these elements:

  • Click on the appropriate header area. Let us first enter the middle part of the header.
  • Now, go to the ‘Design‘ tab in the ribbon and select the element to insert, for example, FileName. Click on the option – File Name, and as a result, excel would insert the word &[File] there.
  • To see the exact header text, click anywhere on the worksheet.
File Name on Header

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:

  • The first one is to use the right scroll bar to move downwards until you see the footer.
  • The second and the most prominent one is to use the option – ‘Go To Footer‘ (Design Tab). By clicking on this, you would instantly jump to the Footer part.
Go To Footer Navigation

Using Combination of Elements to Customize Headers and Footers

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.

Using Multiple Elements - Demonstration
Header and Footer in Excel

Insert Picture or Image in Excel Header or Footer

Follow Below Steps to Insert Image or Picture in Header and Footer in Excel

  1. Navigation

    Click on the appropriate header or footer area and go to the ‘Design‘ Tab.

  2. Picture Element

    Under the ‘Header and Footer Elements’ click on the element that says – ‘Picture‘.

  3. Insert Picture Dialog Box

    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.

Format Picture Element

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).

Format Picture Dialog Box

Add Headers and Footers in Multiple Sheets at One Go

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.

Other Miscellaneous Options In Design Tab

There are four miscellaneous but very important checkboxes in the Header and Footer Design Tab which are explained below:

Miscellaneous Header and Footer Options
  • Different First Page – When you want to have a different header or footer for the first page, tick this checkbox and then give a separate header and footer for the first page. For the other pages, you need to again give a header or footer only on the second page and it would be applicable for all the pages (except for the first page).
  • Different Odd & Even Pages – As the name suggests, tick this checkbox, if you want that the odd pages (1, 3, 5, and so on) should have different headers/footers than the even pages (2, 4, 6, and so on).
  • Scale with Document – The scale with document checkbox is ticked by default. A tick means when you change the scale of the document (while printing), the header and footer font size will also change accordingly. To stop changing the header and footer size while scaling the document for printing, uncheck this option.
  • Align with Page Margin – It is better to have this option activated so that the headers and footers are aligned according to the page margin.

How to Change Font Size and Color of Header and Footer in Excel

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.

Font Size and Color Change in Header or Footer

Show or Hide Header and Footer in Excel

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.

  • In the status bar, you can toggle between the ‘Normal’ and ‘Page Layout’ button to show or hide the Excel headers and footers.
Normal and Page Layout View - Status Bar
  • The same options are available in the ‘View‘ tab in the excel ribbon, as shown below.
Normal and Page Layout View - Ribbon Option

Remove or Delete Header and Footer in Excel

Finally, let us learn multiple ways to remove the header and footer from Excel worksheet.

  • Simply click on the header or footer and press Delete or Backspace key on your keyboard to remove the header or footer from all the pages.
  • Another way is to go to the ‘Page Setup’ dialog box and set it to ‘(None)‘, as shown below:
Setting up Headers and Footers to NONE

With this you have reached to the end of this blog. Share your thoughts, views, and comments in the comments section below.

The post Excel Header and Footer – Add, Change, Customize appeared first on Excel Unlocked.


Advanced Filter in Excel – A Rarely Used Filter Feature

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.

Sample Data

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.

Advanced Filter in Excel - Sample Data

What is Excel Advanced Filter?

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.

Normal Filter v/s Advanced Filter

Below is the list of difference between the normal AutoFilter feature and the Advanced Excel Filter feature-

  • When you filter the data set or excel data table, excel would not remove duplicate records. All the duplicate records will be visible. Unlike this, the advanced filter feature has an additional benefit of removing the duplicate records from the dataset. As a result, you get a unique list of data records, after filtering the dataset.
  • The regular Filter option does not allow you to give cell reference to filter the data. However, by using the advanced filter option, you can filter the dataset by giving reference to the cell as a filter criterion.
  • When you apply Filter/AutoFilter on the dataset, the existing dataset itself gets filtered (at the same location). However, the advanced filter option allows you to filter the dataset and place it at some other location in the workbook (without touching the existing dataset).
  • As learned in the definition section, the Advanced Excel Filtering feature can do more complex filtering to the dataset.

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.

Where is Advanced Filter Feature in Excel?

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)

Navigation to Advanced Filter in Excel

As soon as you click on the ‘Advanced’ option, the Advanced Filter dialog box would pop out on your screen.

Advanced Filter Dialog Box

Advanced Filter’ Dialog Box – Explained

The Advanced Filter Dialog Box has following options in it-

  1. Action – There are two check boxes over here. The first one is – ‘Filter the list, in-place‘ which filters the existing data set table at the same place/location. Another checkbox is ‘Copy to another location‘. This copies the data set at another location and filter it at this new location, therefore, keeping the original data set intact.
  2. List Range – Here you need to specify the data set or excel table range. Make sure to include the header row in the range selection.
  3. Criteria Range – Here you need to specify the reference to the range of cells that contain the filtering criteria.
  4. Copy To – When you want to copy the filter data to another location, you need to specify the destination location range over here.
  5. Unique Records Only – Tick this checkbox if you wish to remove duplicate records while filtering the dataset.

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).

Remove Duplicate Records in Excel Table

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:

Duplicate Records - Data

Follow the undermentioned steps to remove the duplicate records:

  • Firstly, select the data set (in our case A3:E22). Make sure that you also include the header rows in the selection. If you do not select headers, then Excel would consider the first row in the selection as a header row.
  • Next, open the ‘Advanced Filter’ dialog box (Data Tab > Sort & Filter Group > Advanced).
  • Here, you would notice that the excel has itself filled the ‘List Range‘ input box as the selected data table.
  • In the ‘Advanced Filter’ dialog box, select the radio button ‘Copy to another location‘. In the ‘Copy To‘ section, specify the cell reference of the destination location. Let us copy this at cell G3. Finally, tick the checkbox that says – ‘Unique records Only’.
Unique Records Only

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.

Unique Records Only Demonstration

Filter Data Based on Criteria

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-

Copy and Paste Table Headers for Complex Filtering

Make sure that the table headings text in the copied area should be exactly the same as that of the original dataset.

Basic Filtering Using Advanced Filter Option

Suppose, you wish to filter out all the records for ‘Female’ candidates or applications. Follow the under mentioned steps:

  • Firstly, enter the criteria below the relevant heading at the copied header data. As we want to filter by ‘Female’ applications, enter the word ‘Female’ in the cell J2.
Entering Filtering Criteria in Copied Area
  • Click anywhere in the original data set and then open the ‘Advanced Filter’ dialog box using the path – Data Tab > Sort & Filter Group > Advanced.
  • In this dialog box, do the following-
    • Select the radio button ‘Copy to another location‘ under the Action section.
    • In the ‘List Range‘ input box, the original data set range would automatically appear. If it does not appear, then enter the original dataset cell range ($A$1:$E$18).
    • Enter or select the criteria cell range (including the criteria headers and value) i.e $G$1:$K$2 in the ‘Criteria Range‘ section
    • Int eh ‘Copy To‘ area, Enter the cell reference of the destination cell where you want to paste the filtered data (let us do it at cell G5).
Simple Advanced Filter Using Criteria #1

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.

Criteria Filter Using Advanced Filter Option - Demonstration

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.

Filter Using Two Criteria - Advanced Filter

Complex Filtering (Using AND and OR Operators)

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:

  • If you want to apply AND rule, then place all the AND criteria in a single row.
  • Where you want to apply OR rule, then place the criteria in another/next row.

Suppose, we want to filter out all the records for Masters or Phd programs enrolled by only Male Candidates.

Complex Advanced Filter

Over here the ‘Criteria Range’ would be $G$1:$K$3.

Advanced Filter Feature in Excel

Logical Operators and Advanced Filter

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:

Text Filter Using Logical Operators

Criteria ExplanationExample from Sample Data
wordShow the data that begins with the specified wordEntering Bachelors will show all the data that begins with this word
=wordShow the cells that exactly matches the wordEntering =”=Bachelors of Commerce” will show the cells that exactly contain this word
<>wordShow the cells that do not exactly match the wordEntering <>Bachelors of Commerce will show all the cells that do not exactly contain this word
>wordShow the data that are alphabetically after word.Entering >Masters in Business Administration will show data that comes after this word alphabetically
<wordShow the data that are alphabetically before wordEntering <Masters in Business Administration will show data that comes before this word alphabetically

Date and Number Filter Using Logical Operators

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:

Filter Data Between Two Dates - Advanced Filter

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.

Advanced Filter Using Wildcard Characters in Excel

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 (~).

  • Placing Asterisk (*) before, after, and/or middle of the text means searching for any number of characters before, after, and/or middle.
  • Similarly, placing Question Mark (?) before, after, and/or middle of the text means searching for a specific number of characters before, after, and/or middle. A specific number of characters here means the
  • Tilde (~) before *,? or ~ means searching for the text which actually contains a wildcard characters *,? or ~ as a part of the text.

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.


Filter in Excel – Add, Apply, Copy, Clear, and Many More

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.

Sample Data – Download Sample File

In this blog, we would be using the below data. Kindly download and practice along with using the Download button.

Sample Data - Filter Feature in Excel

What is the Purpose of Filter Feature in Excel?

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.

Adding Filter Option on Header Rows

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.

Header Row in Data Set

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:

  • Go to ‘Home‘ tab > ‘Editing‘ Group > ‘Sort & Filter‘ Option > ‘Filter‘ button.
Filter Option under Home Tab
  • Go to ‘Data‘ tab > ‘Sort & Filter‘ group > ‘Filter‘ Button.
Filter Option under Data Tab
  • Keyboard Shortcut Method : Ctrl + Shift + L

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:

Filter on Header Row

Simple Ways to Filter Your Data Set in Excel

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.

Applying Filter on Single Column

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.

Filter Drop Down Initial View

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.

Filter by One Column Demonstration

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.

Applying Filter on Multiple Columns

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‘.

Filter by Multiple Columns

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).

Filter Multiple Columns Result

Some Useful Points and Tips

Once a filter is applied on the header row-

  • The Downward-facing arrow on the filtered column changes its icon which denotes that there is a filter applied to this column.
  • When you take your mouse cursor over the filter button, it shows the filter values.
Hovering Mouse Cursor on Filter Button
  • To increase or decrease the width of the filter window, take your mouse cursor on the bottom-right corner of the filter window. Once the mouse cursor changes to a two-side facing arrow, click and drag right (to increase) or left (to decrease). See the below demonstration for more clarity.
Increase Decrease Width of Filter Window

Filter Data Using Filter Search Box in Excel

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.

Filter Using Search Box

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.

Clearing Filter on Columns in Excel

When you want to clear filter from selected or all the header columns in Excel, you can use any of the below-mentioned ways:

  • Click on the ‘Filter‘ icon on the header of the column header, and click on the option that says – Clear Filter from <Column Header Name>
Clear Filter from Column Header
  • Another way is to click on the ‘Filter‘ icon of the header, and check the checkbox – ‘Select All‘.
Clear Filter from Column Header #2

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:

  • Click on any of the cells in the dataset and navigate to the path – Data tab > Sort & Filter Group and there click on the option that says – Clear.
Clear Filter From All Headers #1
  • Or you can even use this – Home Tab > Editing Group. Click the option Sort & Filter > Clear.
Clear Filter From All Headers #2

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.

How to Filter Values or Text in Excel?

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.

  • You can filter the values or text which exactly Equals or which Does Not Equal to some text.
  • Additionally, you can even filter for a non-exact match by using the Contains and Does Not Contain a text
  • Or, you can even filter the values or text that starts or Begins With or Ends With a particular text.

These advanced filter options are available under the ‘Text Filter’ section of the Filter Window as shown in the image below:

Text Filter Options #1

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.

Custom AutoFilter dialog box #1

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.

Filter Values based on Two Criteria

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-

Custom AutoFilter - Two Criteria Filtering

This would filter out those values which either contain the word ‘Commerceor contain the word ‘Science‘.

How to Filter Numbers in Excel?

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:

  • You can filter the exact number or amount by using Equals and Does Not Equal option under the ‘Number Filter’.
  • Similarly, the Number Filter – Greater Than and Less Than allows us to get all values that are more or less than a specific value.
  • Greater Than or Equal To and Less Than or Equal To works in a similar way.
  • Between Number Filter allows you to filter out the values lies between two values (lower and upper values both inclusive)
  • Other options are – Top 10, Above Average, Below Average
Number Filter Option #1

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.

Top 10 AutoFilter Dialog Box
Filter Feature in Excel

How to Filter Dates in Excel?

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.

Date Filter #1

In a nutshell, below options are available for filtering dates in Excel –

  • You can filter the dates which fall in the current month/week/quarter/year and even for previous and upcoming (i.e. next) month/week/quarter/year.
  • Additionally, you can filter out by an exact date, or date that falls before or after a particular date, or between two dates.
  • Excel provides with an option to filter all the dates in a particular month or quarter (regardless of the year in which it falls)
  • You can create a custom date filter using the ‘Custom AutoFilter’ dialog box to which you are already aware of using.

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.

Date Filter #2

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.

How to Filter By Color in Excel?

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.

Filter by Color

Let us, for example, filter the programs by Red font color. The result of the filter would be as below.

Filter By Font Color - Illustration

Quick Way to Filter By A Specific Cell’s Value, Font Color, Cell Color or Icon

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:

  • Right-click on the cell that contains the color by which you want to filter. Let us, in this case, filter by yellow color. Therefore, I have right-clicked on C4 (as it has a yellow cell color).
  • Take your mouse cursor to the option that says – ‘Filter‘, and click on the option – ‘Filter by Selected Cell’s Color‘.

As a result, excel would filter the dataset based on the cell color – Yellow, as demonstrated in the below image:

Filter By Selected Cell's Color - Illustration

A Solution To AutoFilter Does Not Work After Changing Data

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:

  • Using Filter Window – After making changes in the filtered data, simply click on the ‘Filter’ drop-down arrow on the header cell and click ‘OK’ in there. However, there is are exceptions to this method. This would not work when you apply ‘filter by color’ or ‘Number Filter/Text Filter/Date Filter’. This technique only applies when you use the search box to filter out the data.
Reapply Filter #1
  • Using Reapply Option – After making changes in the filtered data, simply navigate to the Data tab > Sort & Filter Group > Reapply Option. The keyboard shortcut key for the same is Ctrl + Alt + L.
Reapply Filter #2

This option is also available in the Home Tab > Editing Group > Sort & Filter Option > Reapply.

Reapply Filter #3

Copy and Paste only Filtered Data in Excel

There are two possibilities to copy and paste only filtered data in Excel.

Copy and Paste Filtered Data Including Header Row

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.

Copy and Paste Filtered Data Excluding Header Row

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).

Removing Filter From Headers in Excel

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 easiest way is to use the keyboard shortcut – Ctrl + Shift + L.
  • Another way is to use ribbon path – Data Tab > Sort & Filter Group > Filter option OR Home Tab > Editing Group > Sort & Filter Option > Filter Option.

The post Filter in Excel – Add, Apply, Copy, Clear, and Many More appeared first on Excel Unlocked.


Link to Category: e-Learning Blogs

Or if you prefer use one of our linkware images? Click here

Social Bookmarks


Available Upgrade

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?


Blogging Fusion is ranked as one of the oldest and strongest directories online!