Category: Excel Tips

A drop-down list in Excel helps you input data correctly and properly from a list of pre-defined inputs. It works as a data validation check and avoids any chance of inputting invalid data. As it restricts the values that can be entered into a cell, it is one of the most used functions of Excel.

A drop-down list carries a list of multiple inputs and makes data entry time-saving and error-free. However, you can remove the drop-down list when you have finished data entry or if you do not want to limit the user to a small set of values.

Apart from the benefits that a drop-down list has to offer, it takes a fine deal of effort to remove a drop-down list from Excel.

Removing any existing drop-down list or multiple drop-down lists from your Excel spreadsheet is not as easy as just selecting the cell and pressing the Delete key.

To know the exact steps involved in deleting a drop-down list from Excel, continue reading the article that follows.

There are three ways how you can remove a drop-down list in Excel – let’s look into each of them.

1. Data Validation

This is the most commonly used method of removing drop-down lists from Excel. Take a look at the example below.

The above example demonstrates the sales record of a store, displaying various brands of mobile phones, along with their prices. Every phone brand has its unique product ID.

Column F represents the payment methods used for selling these mobile phones. The column ‘Payment Type’ contains a drop-down list of various options used to receive payments against mobile phone sales.

If you want to remove the drop-down list in cell F4, the Data Validation dialogue box is the best option. To learn how you can remove the said drop-down list using the data validation technique, follow the steps below.

Step 1

Select the cell containing the drop-down list, which is F4 in this case.

Step 2

Go to the Data tab in the Ribbon and select Data Validation from the drop-down menu that appears.

Step 3

Select the “Clear All” button and press the “Ok” button. Make sure to have selected the “List” option under the “Allow” tab.

This removes the drop-down list from cell F4. Also, the arrow at the right side of cell F4 has been removed, as shown in the following image.

Pro Tip: 
This method removes the drop-down list from any cell but keeps the last values selected from the list within the cell. So, if you have used the drop-down list for easy data entry and later want to remove the list, this option would work best for you. Using Data Validation, you can remove the drop-down list without having to lose the cell values.

How to remove the drop-down list from multiple cells all at once?

If you want to remove multiple drop-down lists simultaneously, you can still do it by using Data Validation.

Select the cells from where you want to remove the drop-down list and repeat the above-mentioned steps.

Excel would remove drop-down lists from all the selected cells while preserving the cell values.

How to remove all drop-down lists from a spreadsheet at once?

If you want to remove all the drop-down lists from your worksheet, the process remains the same.

All you need to do is check the “Apply these changes to all other cells with the same settings” option from the Data Validation window, as shown below:

Selecting the “Apply these changes to all other cells with the same settings” option automatically selects every cell in the worksheet containing the specified drop-down list.

Thereafter, clicking “Clear all” and then the “OK” button will remove all those drop-down lists without you having to lose the cell values.

2. The Clear All Button

This method is helpful if you want to remove the cell’s content along with the drop-down list.

Step 1

Select a single cell or multiple cells from where you want to have the drop-down lists, as well as the cell values, removed.

Step 2

To delete drop-down lists from your worksheet in Excel using the ‘Clear All’ option, take the route established below.

Home Tab > Ribbon > Clear > Clear All

The ‘Clear All’ option clears everything from the selected cells – this includes the values, the formatting, the drop-down lists, and everything else.

3. Copy-Paste Method

Another quick way to remove drop-down lists in Excel is to copy a blank cell from your worksheet and paste it over the cell or the range of cells that have the drop-down list.

Since you’ve pasted an empty cell, the target cell will turn empty and will not contain any drop-down list or other value.

Conclusion

The drop-down list acts as the easiest data validation check when inputting large volumes of data. Adding drop-down lists to Excel is super easy. However, once added, it might be a little tricky to get rid of them.

The above techniques will help you steer your spreadsheet clear of any unwanted drop-down lists. Hope this helped you learn what you’ve been looking for.

Happy Excel Exploring!

Have you ever worked on a set of data with blank rows in between? It can be pretty annoying, right?

Oftentimes, we do encounter this format of data whenever we export data from a system or a database.

With the blank rows in between, it’s quite difficult to sift through the data as you have to scroll further down just to see the rest of the records.

You can’t even select all the data by pressing CTRL + A. Notice that Excel doesn’t recognize the records after the blank rows as part of the data set.

With this data arrangement, you won’t be able to properly filter the records or even create charts.

In short, these blank rows are definitely making things difficult.

In this article, I’ll show you the quickest ways to solve this problem.

Method 1: “Go to Special” Option

To hide blank or null rows, the first step is to select the data set.

Since we can’t make use of CTRL + A to select it, we can do either of the following:

• Select the entire columns of the data set (as shown below).

• Or, select the first cell and scroll down until you find the last cell of the data set.

Then, while pressing the SHIFT key, click on the last cell.

If you don’t have other data at the bottom of the data set, you may want to use the first option as that is the quickest way to select the cells.

Once the cells are highlighted, press CTRL + G.

The Go To menu should appear. Once you see it, click on the Special… button.

You will then see the Go To Special menu. Select Blanks and click OK.

Note that the blank or null cells are highlighted.

Right click on one of these highlighted cells and select Delete…

The Delete menu will appear. Select Shift cells up and press OK. 

And voila! Blank rows are removed from your data set.

Method 2: “Auto Filter” Option

Another option to hide blank rows is to use the Auto Filter. Please know though that this option will require you to copy the data set to a different sheet or workbook. If this is ok with you, then this would be a good option for you too.

To use the Auto Filter, you must first select the cells within the data set.

Same as the steps specified above, you can either:

• Select the entire columns of the data set; or
• Select the first cell up to the last cell within the data set.

Once the cells are highlighted, apply the filters by going to the Data menu and selecting Filter.

You should then see the filters added to the header row.

Once the filters are in place, add a filter so that the blank rows are excluded from the data set.

To do this:

1. Select the filter in one of the fields.
2. Uncheck (Blanks) – this will be the last item in the filters.
3. Click OK.

You will now see the data set without the blank rows in between.

Once you see this, copy the filtered data (CTRL + C) set and paste (CTRL + V) it on a different sheet or workbook.

Conclusion

Blank rows within a data set are oftentimes inevitable especially when you’re dealing with exported data. But with just a few clicks, you can easily remove blank rows by either using the Go To Special or Auto Filter feature in Excel.

Excel is great at number operations and data analytics. However, when it comes to formatting of data, particularly text, you’d find Excel silent about quite a few things. For example, the strikethrough function.

Take a quick look around the Ribbon tab of your Excel workbook to see if something similar to a strikethrough button is visible? Don’t fret if you do not find it there. Unlike Microsoft Word, Excel doesn’t readily offer the strikethrough option through the Ribbon tab.

So what if you want some of your content in Excel to undergo the strikethrough formatting? You may want to do so to show revisions, finality, or cancellation of data etc.

Even though the strikethrough option is not visibly available in Excel, you can still use it in Excel. Also, you may add it to your Ribbon tab. Continue reading the article below to learn how.

2 Ways to Strikethrough in Excel

You may add the strike-through formatting to your data in Excel through either of the following easy methods.

1.   Shortcut Key (Ctrl + 5)

Let us begin with the easiest and the quickest way to get the strikethrough job done in Excel. Simple, use a hotkey.

Take a look here.

To strikethrough the sentence written in Cell A1, select it and press Ctrl+5. There it is, a line passes through the text in Cell A1 as shown below

And what if you only want to strikethrough a certain word from the text in a cell and not the entire text?

Double-click the cell or press F2 to enter the Edit Mode, select the word to be strikethrough as shown below.

Once selected, press Ctrl + 5, and there you go.

2.   Option to Format Cells

Select the cells where you want the strikethrough formatting added and right-click or use the shortcut key combination ‘Ctrl + 1’.

From the drop-down menu that opens up, select Format Cells as shown above.

This would take you to the ‘Format Cells’ dialogue box.

Under the tab Font > Effects > Check Strikethrough > Click ‘OK’

The results are as follows.

Adding the strikethrough button to your Ribbon Tab

Isn’t it a better option to ease the entire situation by adding a Go-to button to the Ribbon tab? Below are the steps that you need to follow to add a ‘Strike-through’ button to the Ribbon tab.

Step 1:

Click anywhere on the Ribbon tab to have the following dropdown menu opened and select ‘Customize the Ribbon’.

Under the head ‘Customize the Ribbon’ select ‘Main tabs’. Double click on Home to extend it down and click ‘New Group’ as highlighted below.

This would add a New Group to the Home Bar, where you can add custom buttons. Next, select ‘Commands not in the Ribbon’ from the ‘Choose Commands’ dropdown menu.

Under the Commands Box, select the Strikethrough function and click ‘Add’ to add it to the New Group.

Click ‘Okay’ to see the button added to your Home Tab as follows.

To strikethrough any data populated in a cell in Excel, simply select that cell and click the Strikethrough button as shown above.

If you use this function of Excel frequently, you may add the Strikethrough button to your Ribbon Bar. This not only saves time and effort but also rids you of the need to remember shortcuts and hotkeys.

Bottom Line

That is all about striking through data in your Excel sheet or adding a quickly accessible button to your Excel workbook. Keep coming back for more helpful articles.

Suggested Tutorial: How to Calculate Average in Excel?

In Excel, you might only use a single worksheet or a combination of many worksheets.

Excel allows you to add up to 255 worksheets in a single Excel workbook. Adding a worksheet is all about a click on the ‘+’ sign that sides right to the sheet tabs on the bottom of your Excel workbook.

However, once you are done working, you might find some Excel worksheets redundant and may want to delete them. The good thing is Excel allows you to remove those unnecessary worksheets from your workbook very easily.

The article below elucidates multiple methods of how you can delete unwanted worksheets from your workbook to shape them to your needs.

1. Ribbon Option

This is the most straightforward and the quickest way to delete a sheet from your workbook.

Consider the following example.

In this workbook, there are four worksheets, namely Sheet1, Sheet2, Sheet3, and Sheet4. If you want to delete Sheet1, follow the steps below.

Step 1

Select Sheet1 with a right-click.

Step 2

Select the “Home” tab.

Step 3

Select the “Delete” option from Ribbon. A drop-down list will appear. Select the “Delete Sheet” option from the drop-down list.

Step 4

A warning box will appear asking to delete the Sheet. Selecting the “Delete” button will permanently delete the selected Sheet from your workbook.

Step 5

Select the “Delete” button, and you have removed Sheet1 from your workbook.

Delete Adjacent Sheets using the Ribbon Option

You can also delete multiple sheets using the Ribbon option. To delete adjacent sheets, press and hold the Shift key and select the first Sheet and the last Sheet that you want to delete.

For instance, to delete sheets from Sheet1 to Sheet3, hold ‘Shift’ and click Sheet1. Do not release the ‘Shift’ button before you click ‘Sheet3’.

In doing so, Excel would select all adjacent sheets starting from Sheet1 to Sheet3.

Now repeat the above-mentioned steps to delete the selected sheet.

Delete Non-Adjacent Sheets using the Ribbon Option

If you want to delete non-adjacent sheets, you can do it using the Ctrl key. Press and hold the Ctrl key and select the Sheets that you want to delete one by one. Now repeat the above-explained method to delete the selected sheets.

2. Right-Click Option

Under this option, deleting sheets in Excel is all about two clicks.

Consider the example below whereby we have four sheets.

From these four sheets, let’s say you want to delete Sheet 2. Here are the simple steps that you must follow in order to delete Sheet 2 using the right-click option.

Step 1

Right-click on the sheet to be removed, which in this case is Sheet2.

Step 2

Choose the Delete option from the list of options that opens up upon right-clicking on the sheet.

Step 3

Select the Delete option from the system Prompt.

The right-click technique can also be used for non-active sheets.

How? If you are working with Sheet 2 and want to delete Sheet 3, you don’t need to go to Sheet 3 for that. Right-click on the tab for Sheet 3 and select delete from the list of options that launches.

Pro Tip:

In addition, you can delete multiple Sheets, either adjacent or non-adjacent, by using the right-click technique. Excel allows users to select multiple sheets in one go by holding the control key simultaneously while sheet selection. Once you’ve selected the desired sheets, you can release the Control key. 

After all the desired sheets are selected, simply right-click on any selected sheet's tab and choose the delete option from the menu to delete all the selected sheets. 

3. Keyboard Shortcuts

Worksheets in Excel can be deleted using keyboard shortcuts. There can be two techniques to do so i.e. a Hybrid Keyboard shortcut, and the other is a Regular keyboard shortcut.

Let us see both techniques one by one.

3.1. Using a Hybrid keyboard shortcut

The hybrid keyboard shortcut uses, both mouse and keyboard combinations to delete any Sheet from the workbook. The Hybrid keyboard shortcut is:

Right-click + D

To delete any sheet or sheets in Excel, right-click on the Sheet you want to delete and press the ‘D’ key from your keyboard. It is faster than using only right-click and selecting Delete from the menu.

3.2. Using a Regular Keyboard Shortcut

You can delete any active worksheet using only the keyboard. For this, the keyboard shortcut is:

ALT + H + D + S

In this shortcut, you do not need to press the keys simultaneously, but one after another. The only limitation is that you can delete only active worksheets by this shortcut.

3.2.1. Using Legacy Keyboard Shortcut

If you remember, before ribbon style, keyboard shortcuts were used to execute various functions in Excel. For compatibility reasons, these shortcuts are still applicable to newer versions of Excel. Such legacy shortcuts still work for deleting any sheet from the workbook. The keyboard shortcut is:

ALT + E + L

For many reasons, this shortcut is faster than many other techniques. Just press the said keys in succession, and the active sheet would be deleted.

Related Tutorial: How to Delete Columns in Excel

Conclusion

A worksheet is an area that allows you to store your data and use various functions to fetch the desired results. Excel allows you to add a fine number of worksheets to manage your data.

Likewise, you can remove unwanted sheets once you are done working in Excel. The foregoing techniques are expected to help you with deleting worksheets through normal Excel working practices.

A little practice can help you master the foregoing techniques in no time!

The #NUM error in Excel practically means there’s something wrong with the numeric values in your Excel formula. This error, however, can mean different things depending on the context of the Excel function used.

In this article, I’ll show you all the possible reasons why the #NUM error appears and what you can do to fix it.

What causes the #NUM error in Excel?

The #NUM error gives us a bit of a clue of what’s causing the error – the numeric values.

The error could either be caused by:

• one of the values added as arguments in the function; or
• the resulting values after the calculation

You could either:

• correct the arguments added (or the contents of the cells referred to); or
• edit the Excel formula so that it hides the error

As mentioned, the #NUM error can mean different things in different Excel functions:

• The #NUM error in DATEDIF may mean that the end date is lesser than (or earlier than) the start date. It should be the other way around.

• The #NUM error in IRR, RATE, XIRR and other iterative functions may mean that Excel can’t find the result within the set Maximum Iterations and Change.

• The #NUM error in XNPV may mean that:
  • the values do not contain at least one positive and one negative value;
  • the dates come before the starting date
  • values and dates have a different total number of values.

• The #NUM error in PERCENTILE may mean that:
  • the array is empty (or pointing to cells that have non-numeric values)
  • “k” is less than 0 or greater than 1 (note that “k” should be between 0 and 1) 

• The #NUM error in other functions may mean that the arguments provided result in:
  • a number that is either way too small or way too large to display
  • an invalid calculation (cannot be performed)

Fix the #NUM Error

To fix the #NUM error in Excel, the first thing that you should do is identify the probable cause of the error.

To do this:

1. Select the cell that contains the #NUM error.

2. Go to “Formulas” >> “Formula Auditing” section >> “Evaluate Formula”

3. The Evaluate Formula menu will appear. Click on the Evaluate button. Notice that it processes one section of the formula at a time.

4. Click on the Evaluate button again until you see the #NUM error. You will then have an idea of where the error started.

Once you’ve identified the probable cause, the next step is to apply the appropriate solution.

1. DATEDIF Function

When working on the DATEDIF function, ensure that the start date is earlier than the end date. Otherwise, you’ll have the #NUM error (as shown below).

In the example above, notice that the DATEDIF formula results in a #NUM error because the start date is greater (or later) than the end date.

You have two options to fix this:

• Update the Start and End Dates so that the Start Date is earlier than the End Date.

• Edit the formula so that it displays something else. Use this option whenever you require your user to input the Start and End Dates. You could have a text like “INVALID INPUT” appear whenever an error occurs. You could use the IFERROR() function for this (same as the screenshot below).

2. IRR, RATE, XIRR, and other iterative functions

The #NUM error in IRR, RATE, XIRR and other iterative functions may mean that Excel can’t find the result within the set Maximum Iterations and Change.

As you may already know, iterative functions run calculations over and over using previous results.

Depending on the numbers you are working on, you may need to set the limit for the number of times that Excel runs the iterative functions so that you’ll achieve your desired result. Because if not, you may experience the #NUM error.

To do this:

1. Go to File >> Options

2. In the Excel Options menu, select Formulas. From there, adjust the Maximum Iterations and Maximum Change. 

• In the Maximum Iterations textbox, enter the number of times you want the calculation to recalculate.

• In the Maximum Change textbox, enter the amount of change you’ll accept between the calculation results.

3. See if your adjustments fixed the error. If not, adjust the iteration settings again.  

3. XNPV Function

The #NUM error in XNPV may mean that:

• the values do not contain at least one positive and one negative value;
• the dates come before the starting date; or
• values and dates have a different total number of values.

As you may already know, the XNPV function returns the net present value for a schedule of cash flows. The series of payments must at least have one positive value and one negative value (for the cost).

When working on this Excel function, ensure that the dates come after the starting date.

You could either:

• Adjust the start date so that it comes before the dates in the selected date range; or
• Adjust the date range so that only those that come after the start date are covered

See to it that the values and dates have the same number of values. If not, you’ll have the #NUM error.

4. PERCENTILE Function

The PERCENTILE function, as you may already know, returns the “k” percentile of values in the specified range.

The #NUM error may appear in this function if:

• The array is empty (or pointing to cells that have non-numeric values)
• “k” is less than 0 or greater than 1 (note that “k” should be between 0 and 1)

To fix this:

1. Ensure that the range selected for the array argument is not left blank and contains numeric values.

2. “k” should be a number between 0 and 1. It can’t be negative (less than 0) nor greater than 1.

5. Other Excel functions

For other Excel functions, the #NUM error may either mean that:

• a number that is either way too small or way too large to display
• an invalid calculation (cannot be performed)

The screenshot below shows some of the reasons why the #NUM error appears in an exponential formula (^):

As you can see, the #NUM error appears when:

• both the base and exponents are left blank or are equal to 0; or 
• the arguments result in an infinite number.

To fix these errors, you may need to change the arguments so that the calculation becomes valid. You may want to try the formula on a calculator and see if computation is possible.

The #NUM errorin the SQRT() function may appear if you try to get the square root of a negative number.

To fix this, you could update the formula to get the absolute value of the number (disregarding the negative sign, if any) before the square root.

You can use this formula: =SQRT(ABS([range]))

Note This:

The #NUM error can be a bit tricky to fix, but you could follow these four simple steps to make it bearable:

1. Use the Evaluate Formula option to find where the error started in your formula. 

2. Once identified, check if the arguments for that Excel function are valid – see if the ranges referred to all contain numbers (if numbers are required), etc.

3. If the arguments are all valid, manually perform the calculation. See if it is possible to do the computation with the numbers provided. You may want to use a calculator to help you with this.

4. If an error is inevitable, you could replace it with a text or a number (e.g., 0) using the IFERROR() function.

Conclusion

The #NUM error essentially means there’s a problem with the numeric values – either with the arguments added in the formula or the resulting values after the calculation.

To fix this, identify the probable cause of the error using Excel’s Evaluate Formula option. Once that is figured out, apply the solution fitting for the cause of the error.

Ever had to insert sequential series of data for hundreds or thousands of cells in Excel?

You can copy the content of a cell to a couple of other cells using the copy and paste function, but how can one autofill a series in excel?

To save the undue effort of manually filling in data, Excel offers its users the Fill Handle feature. It allows you to autofill a series by simply dragging down a small icon.

You can even choose how and what values you want to be entered to the cells that follow.

That’s not all. Dive in to find out all about Fill Handle in Excel and how to use it.

What is The Fill Handle in Excel?

The Fill Handle is a versatile tool that enables autofilling of a series of data based on a certain pattern. Fill Handle in Excel is installed by default and is widely used for the purposes of data entry and transformation.

It is represented by a small rectangular box at the bottom right corner of a cell and comes up as soon as the cell is activated.

For instance, you want to input numbers from 1 to 10 in cells A1 to A10. Instead of inserting each digit individually, simply enter the first two numbers and drag and release the fill handle to input the remaining numbers.

Sounds easy, no?  This is how it works.

Once you have input the first two or even the first value of your intended series, move your cursor to the right bottom of the cell, and drag the Fill Handle until you want the series to continue.

How to Use Fill Handle in Excel?

Using the Fill Handle in Excel is pretty quick and simple. It will generate succeeding values for a series of data on the basis of a certain pattern.

However, if a suitable pattern is not set or is not recognized by the Fill Handle, it will simply copy-paste the value from the first cell to the following cells. Let’s see an example to fill handle in Excel.

Suppose you need to enter the first ten odd numbers in a single column. Select cell A1 and input ‘1’ in the formula bar as demonstrated below.

If you don’t enter the subsequent number ‘3’, and drag the fill handle, Excel will simply paste the digit ‘1’ in the next nine values. That’s because a single value was not adequate for the fill handle to recognize the odd numerical order. The result will be as follows:

Now let’s add the next digit in the odd series i.e. ‘3’ and then use the fill handle.

As evident, the Fill Handle immediately picked up the sequence on the addition of 3 and continued the series while incrementing each value by 2.

Pro Tip: Sometimes it’s the case that even after adding the first two values of the intended series, upon capturing and dragging the Fill Handle, Excel would only return the first two values copied and pasted instead of filling in the series.

To combat the foregoing problem, look out for an ‘AutoFill Options’ icon appearing at the bottom right of the last cell as highlighted below.

From the drop-down menu that opens upon clicking the AutoFill button, select the Fill Series Option and Excel would replace the copied and pasted values with auto-filled series.

The Double Click Feature

Another plus point of the Fill Handle in Excel is the double click feature that works only when there are values in its neighboring rows or columns.

For example, if you select a cell and double-click the fill handle, Excel will automatically fill the series until the last value is in the adjacent column or row. Here is how it works.

Cell B2 contains ‘1’ and is adjacent to the column containing the first ten alphabets. If we double-click the Fill Handle now, it will fill the column automatically until the last value of Column B becomes adjacent to the last value of Column A, as shown below.

This helps when you have to copy values to hundreds of cells, and dragging the Fill Handle becomes tiring. You can double click, and Excel will fill the series up till the cell adjacent to the neighboring cell of the other column that contains values.

Also, instead of incrementing, the Fill Handle can fill values in a decrementing sequence if you input the first two values in the same order i.e. 10 in the Cell B1 and 9 in the Cell B2.

Autofill Menu for a List of Items

Fill Handle is a resourceful feature of Excel that offers users with a diverse range of options. As soon as you’ve used the Fill Handle feature, the ‘Auto Fill Options’ icon appears at the bottom right of the series as evident in the screenshot below.

On clicking the icon, Excel displays a list of actions that can be performed depending upon the type of data under discussion (numbers, days, dates, etc.). Let’s look into how you may use each of these options.

1.  Numbers – AutoFill Options

For a list of numbers, it shows options as highlighted below:

Let’s see what these options are used for as below:

Copy Cell: It fills the series by copy-pasting the contents of the first cell of the series.

Fill Series: It is the option set by default. It allows the Fill Handle to understand the pattern of values appearing in the initial cells and complete the series in accordance with the same.

Fill Formatting Only: It is used when some formatting has been applied, like assigning colors, creating tables or charts, etc. It copies the formatting throughout the series but does not copy the values.

Fill Without Formatting: It works opposite to the ‘Fill Formatting Only’ option as it copies the values only and leaves out the formatting.

Flash Fill: It is a plus feature of autofill that is extremely useful when you need to work on a tons of data. It automatically enters corresponding data from adjacent cells to the active cell. Let’s see an example of how this works.

Column A has all the data to be entered into the neighboring columns. If you type ‘Jane’ (the first part of the value in A1) in the adjacent cell, double click the fill handle and choose Flash Fill from the autofill option; here’s what will happen.

The same would be true for column C containing the surnames as:

Alternatively, if you type ‘Jane Joseph’ (the complete value in A1) in the adjacent cell, double click the fill handle and choose Flash Fill from the autofill option; Excel would flash fill Column B with the complete values as appearing in the adjacent cells of Column A.

2.   Days – AutoFill Options

When entering days, Fill Handle offers options as highlighted in the screenshot below:

AutoFill Weekdays: It allows the Autofill to only input the weekdays and not weekends as evident below:

So after the last weekday, Friday, it starts again from Monday.

AutoFill Days: Contrary to the above ‘Fill Weekdays’ option, the option to ‘Fill Days’ fills the series with all the days of a week.

3.   Dates – AutoFill Options

The Fill Handle of Excel allows users to enter dates following a specific pattern and provides with options as shown in the screenshot below.

For instance, for the Fill Handle to fill the first ten dates of January, you will have to select the Fill Days option as follows:

Similarly, you can fill in years or months in the same way, using the Fill Months and Fill Years option from the autofill. Fill Handle increments each entry by 1.

How Do You Enable the Fill Handle in Excel?

The Fill Handle feature in Excel is available by default, and you don’t need to go looking for it in different tabs. However, you might possibly have disabled the feature mistakenly. You can find it in:

File > Options > Advanced

Make sure the Enable Fill Handle option is checked. Click Ok, and you’re good to go.

Conclusion

Fill Handle is one of the simplest and quickest features to use that makes your work and time 10x more productive. A single double-click fills hundreds of cells in less than a second.

That brings us to an end. We hope you find this article to be as helpful as we crafted it to be.

Happy Excel Learning!

 Suggested Tutorial: How to Square a Number in Excel?

Pivot Table is one big reason why statisticians, data engineers, actuaries, and other professionals from data-intensive fields of work would resort to Excel.

Even if you do not know coding or programming, still you can work out huge volumes of data in Excel using the Pivot Tables.

And all you need to do for this is have a fine grasp of how to construct a Pivot Table in Excel.

Pivot tables are undoubtedly great analytical tools that help you organize and summarize your data with a few clicks. And after you’ve analyzed the data as required, you may not want to keep the Pivot Table on your worksheet forever.

This is mainly because if you have huge amounts of data populated in your pivot table, it may hamper the performance of your workbook. It takes up large file space and can slow down the performance of Excel.

Excel users, therefore, prefer deleting Pivot Tables once they have sought the results they were looking for.

However, deleting a Pivot Table may not turn out to be as easy as creating a Pivot Table.

In this article, we will show you the four possible ways how you may delete a Pivot Table from your workbook with sheer ease. To learn more details about each of these methods, continue reading the article below.

1. Remove the Pivot Table Along with the Source Data

If you want to remove the pivot table along with the data contained therein, you can go with a number of methods to do so.

Consider the following pivot table:

Method # 1

The simplest way to delete such a pivot table is to delete the entire sheet that contains the subject pivot table and you are done. However, this method is only applicable if the sheet doesn’t contain any other data that you may want to preserve.

Method # 2

Another way to delete this pivot table is to activate any cell from this pivot table and press CTRL+A. Doing so will broaden the selection to the entire pivot table.

Next, press the delete key to get rid of the pivot table.

Pro Tip: If your Pivot Table consists of any filters, you may not want to opt for this method. 

Method # 3

Select the entire pivot table using the shortcut key ‘Ctrl+A’ and select the Clear All option through the following route.

Home Tab > Editing > Clear > Clear All

Method # 4

Using The Pivot Analyze Tab

• Click any cell in the pivot table.
• Select the Pivot Analyze tab.
• Click on the Select option.
• Select the Entire Pivot table from the drop-down list. The whole of the Pivot table will be selected by Excel.
• Press the Delete key from the keyboard, and you are done with deleting the pivot table along with all data

Method # 5

Using Shortcut Keys

Let’s bring your keyboard to action. You can also delete a Pivot Table in Excel by using a combination of different shortcut keys. To do so, follow these steps:

• Click any of the headers in the pivot table.
• Press CTRL + A to select the entire pivot table.
• Press ALT + E to open the Clear option.
• Press the A key, and the pivot table will vanish along with its data.

2. Delete the Pivot Table but Keep the Source Data

It is at times that after analyzing your data you want to keep the sorted data but not the Pivot Table itself.

For instance, if you have a large data file, the pivot table might become too heavy to bloat your workbook. Resultantly, you may want to remove the pivot table to reduce the size of your Excel workbook.

Let’s take the Pivot Table in the image below as an example. To remove the pivot table in the image below but keep the data, you may take either of the methods stipulated below.

Method # 1

• Activate any cell from the pivot table.
• Select the Pivot Analyze tab.
• From the Action group, choose the Select option.
• From the drop-down list, select the “Entire Pivot table” option. This selects the entire pivot table.
• Go to the Home tab.
• From the Clipboard group, select the Copy option to copy the entire Pivot table.
• Next, click on the Paste option or use the shortcut key ‘Ctrl + V’ after activating the cell where you want the data placed.
• In the drop-down menu for Paste options, select the ‘Value’ option.

Excel will remove the pivot table but keep the data contained in the Pivot table.

Method # 2

Another simpler way of deleting the pivot table while preserving the underlying data is through shortcut keys. Follow the steps listed below.

• Click any header of the pivot table.
• Press CTRL + A. to select the entire pivot table.
• Press CTRL + C to copy the pivot table.
• Press ALT +V to launch Paste options from the Ribbon area.
• Press V to remove the pivot table and paste the data values into respective cells. And you get only the resulting data from the pivot table.

3. Delete the Source Data but Preserve the Pivot Table

In the image below, a Pivot Table is created that consists of filters and headers for data analysis.

After using the analytical data from the pivot table, if you want to delete the resulting data while keeping the pivot table to use for data analysis later, follow the steps below.

Method # 1

• Select any cell from the pivot table. Select the Pivot Analyze tab.
• From the action group, choose the ‘Select’ option.
• From the drop-down list that then opens up, select the “Entire Pivot table” option to select the entire pivot table.
• Select the Clear option from the action group.
• A drop-down list will appear. Select the “Clear All” option from the list.
• It will clear the data inside your pivot table, and the pivot table is ready to use for newer datasets.

The data inside the pivot table has been removed, and the pivot table is ready to crunch newer datasets.

Method # 2:

Another simpler and faster way to perform the steps above is to use keyboard shortcuts. To delete data from your pivot table while keeping the pivot table using shortcut keys, follow the steps below.

• Click on any of the headers in the pivot table.
• Press CTRL + A to will select the entire pivot table.
• Select the Pivot Analyze tab.
• Select the Clear option from the action group.
• A drop-down list will appear. Select the “Clear All” option from the list.
• It will delete all the data values leaving the pivot table for the next data analysis.

4. Delete All the Pivot Tables from a Workbook

The above-mentioned methods work well when you have a single pivot table for your worksheet. What if you have multiple pivot tables on your worksheet? It may get super hectic to delete them all one by one.

However, to save you undue effort, we have a short VBA code that will help you delete all the pivot tables in your worksheet in one go.

The code is stated as follows.

Sub ClearPivotTables()

For Each ws In ActiveWorkbook.Worksheets

For Each PvtTable In ws.PivotTables

PvtTable.TableRange2.Clear

Next PvtTable

Next ws

End Sub

For example, to delete the following three pivots table simultaneously, you need to take the following steps.

To make the above VBA code work, follow these steps:

• Select the Developers tab. If you don’t see any Developers tab, you can get it by customizing your Ribbon options.
• Select the ‘Visual Basic’ option from the Ribbon.

• Excel will open up the VBA window.
• Select the Insert tab from the VBA window.
• From the drop-down list select the Module option to launch a module window.
• Copy-paste the above VBA code into this window.
• Close the window, and you are done.

To delete all the pivots tables from your workbook, click the play option from the VBA window. Excel will ask for your permission to run the code. Upon running the code, Excel will delete all the Pivot Table from your workbook in a snap.

Pro Tip:

Once the VBA code is run and the Pivot tables are deleted, you’ll not be able to restore them. So be sure when to use this code, and better create a backup copy to avoid losing these.

Conclusion

There are various methods to delete a pivot table or pivot tables in Excel. Whether you want to delete the entire pivot table along with the resulting data, or only want to delete the pivot table for your workbook to perform efficiently – this article elaborates on all the methods to delete Pivot Tables from Excel.

Keep coming back for more.

If you are currently working on a long list of numbers in your Excel file and are looking for ways to move the decimal places in them, either to the left or right, then you’ve come to the right place.

In this article, I’ll show you the quickest ways to transfer the decimal point from one place to another.

This article will serve as a refresher on our basic math. Yes, we will relearn the formulas taught in primary school. The only difference is that we will learn how to do it in Excel.

Moving Decimal Places to Left or Right

When moving decimal places, there are 2 things that you have to consider:

1. the direction of the movement – whether to the left or the right; and
2. the number of places that you’d like to move the decimal point – it can be 1 or 2 decimal places (or even more)

If you need to move the decimal places to the left, divide the number by the power of 10s.

The table below shows the formulas to use. Note that it varies depending on the number of decimal places to move.

If you need to move the decimal places to the right, multiply the number by the power of 10s.

Once you’ve identified the formula, the next thing to do is set it up in Excel. I’ll show you 2 nifty ways to do this.

Add Formula by Dragging the Fill Handle

1. Select the first cell where you will add the formula (e.g., cell B1) and type the equal sign (=).

2. Click the first cell of the column where your first number is located (e.g., cell A1).
Notice that the selected cell’s address will appear.

3. Type slash (/) for the division operator and asterisk (*) for the multiplication operator. Then type the power of 10s to divide the number by (e.g., 10, 100, 1000).

4. Press ENTER. Notice that the cell will display the same number with the decimal place(s) moved either to the left or right.

5. Once you’re happy with the result, the next step is to copy the formula to the remaining cells below it.

To do this, click on the cell with the formula (e.g., cell B1), then hover the mouse to the bottom right of the cell (the one with a small green square). You’ll notice the plus sign (+) appears. That is the Drag Fill Handle. When you drag this handle down, you will automatically fill the cells based on the cell on top.

Drag the plus sign down until you reach the last cell.

You’ll then see the formula added to the remaining cells.

6. If you want to replace your existing numbers with the new decimals (the ones where the decimal points are moved), copy the cells containing the formulas and paste them as values.

To do this, highlight all cells with the formula and copy it (CTRL + C).

Select the first cell with numbers and press CTRL + ALT + V.

The Paste Special menu will appear. Select Values from the options and click OK.

And that’s it! Your current numbers will be replaced with the updated ones.

You can then remove the cells containing the formulas.

Note This

Note that you should paste the numbers as values to ensure that only the resulting values are copied (not the formulas).

The “Paste Special Operation” Option

The next option is pretty cool and has the fewest steps involved.

All you have to do is:

1. Type the divisor or the multiplicand in one cell (e.g., 10, 100, 1000).

2. Select the cell and copy it (CTRL + C).

3. Highlight all the cells containing your numbers. Press CTRL + ALT +V. From the Paste Special menu, select either Multiply or Divide. Then, click OK.

4. And voila! Your numbers are now updated with the decimal points moved to the left or right.

Conclusion

It’s pretty easy to move the decimal points in a number. All you have to do is remember that:

1. To move decimal places to the left, divide.
2. To move decimal places to the right, multiply.
3. The number of zeroes in the formula represents the number of times the decimal point will move.

You can then choose from the options above your approach for adding the formulas in Excel.

There’s barely an Excel user who is not aware of the Conditional Formatting feature of Excel. For some quick reiteration, it allows users to apply specific formatting to a cell from a specified range that meets a specified criterion.

How is it different from conditional formatting based on another cell? Simple conditional formatting checks a specified range against the supplied criterion and formats the same range. However, if you want to format individual cells or a range of cells based on another cell’s value, this is known as conditional formatting based on another cell.

Read through the article below to learn all about conditional formatting based on another cell in Excel.

How to Apply Conditional Formatting Based on Another Cell in Excel?

Before we delve into further details, it is to be noted that conditional formatting based on another cell’s value is not a function different from simple conditional formatting in Excel. In fact, it is only a smart way to work out the basic conditional formatting function of Excel.

Under simple conditional formatting, cells are formatted based on their values or a value that you specify. To format cells based on the value of another cell, a formula needs to be devised. Let’s see how.

Using a formula

Conditional formatting in Excel through a formula is pretty simple as it is logically constructed. Following are the basic steps to be followed for conditionally formatting a cell based on another cell’s value.

Step 1:

Select the cell or range of cells that you want to format.

Step 2:

On the Home Tab, follow the path below to set up a new rule.

Home Tab > Styles > Conditional Formatting > New Rule

This will open up a ‘New Formatting Rule’ window as shown below.

Step 3:

From the option ‘Select a Rule Type’, select the option ‘Use a formula to determine which cells to format’ as shown below.

As highlighted in the image above, the second box headed ‘Edit the Rule Description’ is where the rule is to be set up.

For instance, if you want to format cells based on the value of cell A10, i.e. highlight all cells greater than the value contained in cell A10, you may compose a rule as follows.

= [Cell Range] > $A$10

Step 4:

Next is to choose the Format style that you want to be applied to the cells that meet the specified criterion. To do so, click on the Format button appearing at the bottom right of the window as highlighted below.

This would take you to the ‘Format Cells’ window.

Here, you can toggle between different formatting options ranging from font style to border style to color fill and so on.

Once you have set up the desired formatting settings, click ‘okay’ to preview how the cells would look when formatted.

If everything looks good, go ahead to hit ‘Ok’, and Excel would format the cells that meet the specified conditions.

A Simple Example:

Up till now, we have learned the process to be followed to conditionally format cells based on the values of other cells. Now it’s time we demonstrate the same through a short and basic example.

The data above shows the inventory levels of different products. The minimum inventory level maintained by the Company is 100. How can we find out the products for which the inventory level is below the minimum inventory level threshold?

Here, we want to highlight the Product Items based on the level of inventory maintained for each of them. We can apply conditional formatting to product items by setting the minimum inventory level as the criterion. How? Learn below.

Step 1:

To do so, first, select the row containing the Product items. If you also want the inventory levels to be highlighted, extend the selection to column B too.

Step 2:

Access conditional formatting from the Home Tab as follows.

Home Tab > Style Group > Conditional Formatting > New Rule

Step 3:

From the ‘New Formatting Rule’ window select the option ‘Use a formula to determine which cells to format’ and compose the rule as follows.

=$B2<$B$10

The formula is set up only for the first cell of the column where the formatting is applied. Excel would automatically apply the formula to the entire cell range selected in Step 1.

Logically, this tells Excel to highlight all those cells from the selected range where corresponding cell values of Column B are lesser than the value populated in B10 i.e. 100.

To quickly take a look into the range where the formula applies, the formula, and the formatting style, go to:

Home Tab > Style Group > Conditional Formatting > Manage Rules > Current Selection

This will show a list of all the rules applied to the current selection of cells.

Step 4:

Once the formula is set up, next you need to go to the Format button to specify the formatting you want to be applied to the cells.

We have set a yellow fill from the Format Tab, and the ‘Preview Pane’ shows as follows.

Step 5:

You are all set. Hit ‘Enter’ to see the results as below.

A Quick Brainteaser!

It is often the case that the source data upon which conditional formatting is to be applied is constantly changing. For example, an Excel sheet where a business records all the sales’ transactions might have new sale transactions added every minute.

If you simultaneously want to highlight all the sales that exceed a certain amount, how can you set up a formula that applies to all existing and new entries automatically?

This can easily be done through the conversion of the source data into a table before conditional formatting is applied to it. For instance, the source data in the above example can be converted into a table as follows.

Select the data > Insert Tab > Table

The conditional formatting applied to the table will automatically cover all additions, changes, or deletions made to the table.

Can we specify more than one condition for conditional formatting?

We can specify more than one condition under the conditional formatting function of Excel. However, this requires using the AND / OR operators.

• The AND operator is used when you want Excel to apply conditional formatting to cells only when all the specified conditions are met.
• The OR operator is used when you want Excel to apply conditional formatting to cells when either of the specified conditions are met.

Let’s see an example of this. The image below represents sales of different products in different regions.

From the above series, we want to highlight products that meet the following two conditions:

• Sales under $100; and
• Sales are made in California

In other words, we want to apply conditional formatting to products based on the values of other cells (sales and region).

Step 1:

This can be done by setting up a formula with the AND operator as follows.

= AND ($B2<100, $C2=”California”)

The formula is set to cover two conditions through the AND operator.

• The first condition $B2<$100 specifies that only those sales are to be highlighted that are under $100.
• The second condition $C2=”California” specifies that only those sales are to be highlighted where the corresponding region is California.

Step 2:

After the formula is set up, choose the format you want to be applied to the cells and hit enter.

Excel highlights those products where sales are under $100, and the sale region is California.

It is only this easy to apply conditional formatting based on another cell. Using the AND / OR logical operators, you can compose many formulas with different conditions.

Conclusion:

Conditional formatting based on another cell is a smart application of simple conditional formatting. By setting formulas, there are endless possibilities for the conditions that you can specify to sort your data. Practicing it with a few sets of varied data will help you master the same in no time. Sometimes, copying the formatting can be enough to do the task.