23 Hidden Excel Tricks You Shouldn't Miss!

Key Points:

• Learn 23 hidden Excel tricks for daily work efficiency.
• Discover shortcuts for filling series and manipulating dates.
• Streamline data filtering and custom sorting methods.
• Use Excel options to prevent unwanted data conversions.
• Explore advanced data entry and sheet management techniques.

---

Hello and welcome to another Excel tips video! I am Sumit Bunel, and in this video, I'm going to show you 23 hidden Excel tricks that you can use in your day-to-day work. Now, these are the tricks that I have learned over years—some by accident, some by reading books, and some by watching other people's videos. And while you may know a couple of those tricks, I guarantee you will pick up some new ones. So let's see what these are!

Let's start with a simple trick. Here, I want a sequence of numbers. To do that, I can enter one here, two here, then select both of these cells, bring my cursor to the bottom right part of the selection so it changes into a plus icon, and then I can drag the cursor down. This is called Fill Handle, and it is going to fill a sequence of numbers.

But what you can also do is, instead of entering two numbers, just enter one in any cell. Then bring the cursor here to the bottom right, hold the control key, and see what happens when I hold the control key. The cursor changes into two plus icons; one is a bigger plus icon and the other is a smaller plus icon. Now, when I drag this down, it is going to fill a sequence of numbers. You do not need to fill the two cells; you can just fill one single cell and you can start at any number.

So if I enter five here, then again I come here, hold the control key, and drag this down, it is going to give me a sequence of numbers.

Now, here I have this date, and if I want to get all the months and get the first day of the month, you can simply come to this cell, drag this down, and once you have dragged it down, you'll see this icon here at the bottom right. So click on it and then click on Fill Months. When you do that, it is going to give you the first day of every month.

If you want to get the last day of every month, enter it in one of the cells, then drag this down. I have 31st January 2025 here; I'm dragging this down, and it gives me a sequence of these dates. Now, if I come here and click on Fill Months, it is going to give me the last date of every month. You can see it's 31st January, then 28th February, then 31st March, then 30th April, and so on. It’s smart enough to realize what is the last date of every month.

If you only want weekdays, you can drag this down, and it gives me a sequence of these dates. If I want to remove weekend dates, I can click here and then click on Fill Weekdays. When I click on it, it is going to remove weekend dates which are Saturday and Sunday, and only give you the weekdays.

Now, I have this dataset and I want to filter this data based on column B where I have states. Let’s say I want to get all the records for California. The usual way of doing this is I would first select any cell in this data, go to the Data tab, and then apply filter. Once the filter is applied, I can filter based on the state name. But a faster way to do this is to just select the cell, right-click, and then go to the Filter option in the right-click menu. After that, click on Filter by Selected Cell's Value, and when you click on it, it will instantly filter these records and only show the records of the cell you selected, which was California in this case.

However, there’s an even faster way to do this. If this is something that you need to do quite often, you can actually add this option in the Quick Access Toolbar. Let me show you how to do this. Let me press Ctrl + Z to get the unfiltered data. Now, I’m going to go to the Quick Access Toolbar and click on the Customize Quick Access Toolbar option, and then I’m going to click on More Commands. When I do that, it opens the Excel Options dialog box.

Here I’m going to come to this drop-down and choose All Commands so it shows me all the options that are available to me. Then I’ll scroll down and look for the option called Auto Filter. So here I have this option; I will click on this option and then click on Add. When I do that, it adds it to my Quick Access Toolbar. Now, when I click OK, this icon appears in the Quick Access Toolbar.

Now, one important thing to know is this option here is different from the filter icon option available here. When you click on it, it applies the filter, but when you click on this, it is going to actually filter your data. See what happens—I’ve selected New York here, so I come here, click on it, and it instantly gives me the data for New York.

Excel with Microsoft 365 has a new shortcut to scroll horizontally in your Excel files. So if you just use the wheel on your mouse, it is going to scroll vertically. If you hold the Control key and then use the wheel on your mouse, then it is going to zoom in and zoom out. But if you hold the Control and Shift key and then you use the wheel on your mouse, it is going to scroll horizontally. This is amazing; it is very, very useful!

If you're using Google Sheets, in that case, you can do the same thing but just hold the Shift key—Shift and scroll wheel if you're using Google Sheets, and Control + Shift + scroll wheel if you're using Excel.

Here I have the sales data for all these products in these four quarters, and I want to get the sum of all these quarters. One way of doing this is by using the SUM formula. So I can enter the sum formula, select this range, and then hit enter, and when I do this, it gives me the result here.

But a faster way to do this is by using a keyboard shortcut Alt + =, and it’s called AutoSum. So when you hold the ALT key and press the equal to key—I select this cell, hold the ALT key, press the equal to key—it is going to automatically select this range and insert the sum function. See what happens when I hit enter; it gives me the result.

But what if I want to get this result for all the quarters? In that case, select all these cells and then use the keyboard shortcut Alt + =. It is going to fill the entire row. But what if I want to get this result for all these quarters as well as all these products—the sum of all these products here? In that case, select this data but then select one additional row and column. With this selected, use the keyboard shortcut Alt + =. It is going to realize that all these cells are filled and only fill the blank cells with the sum.

See what happens when I hold the ALT key and press the equal to key; it inserts the sum instantly, and in this case, it is not just giving me the value; it is actually inserting the sum formula.

Let me show you a very fast and easy way to get running totals. Here I have this data and I want to get the running total for these sales. I’m going to select all these sales values, and when I make the selection, you will notice that there is this icon that appears at the bottom right of the selection. This is called the Quick Analysis Tool. When I click on it, it shows me all these options. Go to the Totals option here and then click on the Running Total icon. When you click on it, it is going to insert the formula that gives you the running total.

Let me show you two ways to hide the values in a cell, so that the cell is visible but the value in it is not. Here I have this data where I have the sales values and I have calculated the commission using the value here in cell E1. Now let’s say I want to hide the commission value so that it’s not visible. The common way of doing it is people would change the font color in this cell so that it becomes white, and because the cell background color is already white, this is going to become invisible.

So see what happens when I come here and change the font color to white; this value is now no longer visible. The problem with this method is that if I come here and give this a cell color of any color, this value would become visible because the font color has been changed, but the text is still visible.

A better way to do this is by using custom formatting. Here I’m going to select this cell, and you can select all the cells for which you want to hide the cell value. Then hold the control key and press the one key, and when you do that, it opens the Format Cells dialog box. Here, within the Number tab, click on Custom. You will notice that there is already some formatting applied in the type field. Remove it and then replace it with three semicolons. When you do that and click OK, it is going to remove the value.

Now, one important thing to note here is that while you cannot see the value in the cell, it is still there. You can see it in the formula bar. But even if you come here and give it a cell background color, it will not be visible because it has truly been hidden. So if you’re making any presentations and you have notes or values that you don’t want to be visible, then you can use this trick.

Here is a less-known Excel trick that can be useful in some cases. Let’s say I have this data and I want to get the sum of all these sales values and then I want to use that sum value in a PowerPoint slide or a Microsoft Word report, or I want to copy it and put it somewhere in some other Excel file.

The usual way of doing this would be that I would use a sum formula here and get the sum value, and then copy and paste it. But what you can also do is simply select this data, and then it is going to give you some of these values in the status bar. It gives me the average, count, maximum value, minimum value, and the sum.

Now, if I want the sum here, I do not need to manually type it. What I can do is just use the left mouse key, and when you do that, it is going to copy the sum value to the clipboard. So now when I left mouse click, it has copied this value to the clipboard that can be used in any application. I can use it in PowerPoint, Notepad, Microsoft Word, or in some other Excel file.

See what happens when I come to this cell and use Ctrl + V; it enters the sum value. Similarly, if I select this data and I want the average value, I can just come here, left mouse click, and it has been copied to the clipboard, so now I can come here and use Ctrl + V and it is going to give me the average value. As I said, you can copy and then paste it in any application; it could be outside of Excel as well.

There are a couple of things that frustrate a lot of Excel users; let me show you. Here, if I enter a number that has leading zeros, such as, let’s say 000125, Excel is going to remove the leading zeros. And if I enter something like JAN1, which may not be a date, Excel will think that this is a date and it is going to convert it into a date. And if I enter anything which is something like 1E5, then Excel would think that this is a number with an E with an exponential, so it is going to convert it into an exponential number.

Now, this is something that happens in the back end because Excel has been programmed to do this because this is what most of the Excel users would want. But if you do not want it, you can turn this off. To do that, I’m going to go to the File option here and then click on Options.

In the Excel Options dialog box, I’m going to go to the Data tab and then uncheck this option. So I have Automatic Data Conversions, and if you uncheck this option, it removes all these four options. If you want, you can uncheck them one by one. Let me quickly tell you what these are. This option here, if you uncheck this, it is not going to remove the leading zeros.

If you uncheck this option, then what happens is, when you enter any number that is more than 15 digits, Excel will first convert all the digits after the 15th digit into zero and then turn it into scientific notation. If you remove this, if you uncheck this, then that is not going to happen.

The third thing is, whenever you have something like 1.12E5, when it sees that there is an E in the number, it converts it into a scientific notation. If you uncheck this, then that is not going to happen. And then finally, we had a date, which is JAN1; then it thought that that is a date, so if you uncheck this option, then that is not going to happen.

See if I click OK and go back, I can show you that these things are now not going to get automatically converted. So if I enter 00125, then I have the leading zeros which remain there. If I do JAN1, then it remains as is, so it's not converted into a date. If I have something like 1.12E5, it remains as it is—not converted into a scientific notation.

This is something that you can do. Remember that this is an application-level setting, which means that if you change it for one of your workbooks, it will be applied for the entire Excel application. If you want to turn it back on, you can go back to the Excel Options dialog box and turn it back on.

Here is a data entry trick that might be useful for you. If you are doing data entry and have a number that will always have a specific number of digits after the decimal, then you can speed up the process. For example, if I’m doing data entry and I always have a decimal and then two digits after the decimal, I can speed up this process by making one change in the Excel backend.

So I’m going to go to the File tab here and then click on Options. In the Options dialog box, I’m going to go for Advanced and here I have this option called Automatically Insert a Decimal Point. Now, by default, this option is unchecked, so it’s not enabled. But when you enable this option, it is going to allow you to place a specific number of digits after the decimal.

What it does is, now when I click OK and I enter any number, even without the decimal, it is going to automatically put the decimal before two digits. See what happens when I enter here, let’s say 12345, and I’ve not entered a decimal here, but now when I hit enter, it automatically puts the decimal and two digits are after the decimal. So this can speed up your data entry. You can enter any number, and a decimal is automatically placed for me. Once you are done with the data entry, then you can go back and disable this setting.

Here is another data entry trick for you. Whenever you enter anything in a cell in Excel and then hit enter, the cursor automatically goes to the cell below. But what if you do not want the cursor to go below? What if you are doing the data entry in a row and you want the cursor to go to the right, or maybe stay in the same place? You can decide what the cursor would do when you hit enter.

To change this behavior, go to the File tab here, click on Options, and in the Excel Options dialog box, click on Advanced. Here, the first option that you see is After Pressing Enter, Move Selection and then you can choose the direction. By default, this direction is down, but you can choose it to be right, up, left, or you can disable it.

For example, if I choose right and then click okay, now whenever I do any data entry here and hit enter, the cursor is going to go to the right. If you do not want the cursor to go anywhere, if you want it to stay in this cell, you can go back to the Excel Options dialog box and here make sure that this is unchecked. So when you uncheck this and then do data entry, your cursor is going to remain at the same place.

For example, if I type this and hit enter, the cursor stays at the same place. So the cursor is not going to move. This is again a useful data entry trick for people who are doing data entry and who do not want the cursor to go down.

When you start Excel, you can choose if there is one file or multiple files that should always open whenever you start Excel. For example, if you're working on a project and you always want to start with the project tracker, then whenever you start Excel it would open the project tracker, and it could be multiple files as well.

Let me show you how to do this. Here I have this file test.xlsx, and I want to always open this file whenever I start Excel. This file, as of now, has nothing; it has just a little bit of data and some formatting, but it could be any file on your system.

I'm going to first start Excel, and here I'm going to go to the File tab and click on Options. When I do that, it opens the Excel Options dialog box. Now, I'm going to go to the Trust Center option and then click on the Trust Center Settings button. When I do that, it opens the Trust Center dialog box.

Now here, I’m going to click on Trusted Locations, and here, search for the option which is Excel Startup. So there is this location that says Excel Default Location or Excel Startup. Double-click on this one, and when you do that, it will give you this address. Copy this path, and now I can close this Excel file.

Now I would go to the File Explorer and open this path. When I do that, it shows me this folder. Now, if I put any Excel file in this folder, whenever Excel opens up, that file would also open.

So let me show you. I have this file test.xlsx, so I’m going to copy this file, then go to this folder that the location of which we just copied, and paste the file. When I’m doing this, it is going to show me this prompt, so I'm going to click on Continue. Now it has pasted this file here.

So now I can go back, and this is the downloads folder, but I’ve already pasted the file in the path that I copied. Now, as of now, Excel is not open, so now when I open Excel, see what happens; it automatically opens this file test.xlsx. So it is not opening the blank instance of Excel; it is actually opening the file that I’ve put in that Excel Start folder. Because I just put one file, only this file has opened.

But if you put multiple files there, then all those files would get opened.

While most of the time we are sorting data in columns, which is sorting from top to bottom, Excel also has an option to sort the data from left to right.

So here I have this data in rows, and I want to sort this data. Let’s say I want to sort based on the sales value. So I want the sales value with the highest sales on the left, and then as it goes to the right, the sales values go down. To do that, I’m going to select this data—select the data without the header because we want the header to be where it is, which is in column A.

Then go to the Data tab and here click on Sort. When I do that, it opens the Sort dialog box. Now here we have this button called Options, and when I click on it, you’ll see that by default, the sort orientation is sort top to bottom. If you want to change it, you can change it here. You can select Sort Left to Right and then click OK.

Now I can specify the row by which I want to sort. For example, in this case, if I want to sort based on sales, I can come here, click on Row 3, and then specify Largest to Smallest. Now when I click OK, it is going to sort my data where this is the largest value, and as it goes to the right, the value decreases.

You can also do multi-level sorting. So let’s say I first want all the regions sorted, so all the East, then South, and then West, and then within each region, so within East, I want the data to be sorted in a descending order. You can do that as well.

I’m going to select this data, go to the Data tab, then click on Sort, and here make sure this is Sort Left to Right. First, I want to sort based on the region, so I would select Row 1, and in this case, let’s say it’s A to Z. Then I can add a level.

When I add a level, it is going to first sort based on row number one, and then it is going to sort based on whatever row I select here, which would be row number three. Now I can say Cell Value and Largest to Smallest. Now when I click OK, you can see I get all the values for East here, and then this data is sorted in a descending order, and then all the values for North, and then South, and then West.

This is how you can sort left to right in Excel.

Excel allows you to create your own custom sorting criteria that you can use while sorting the data. In this example, I have this data where I have the name, region, and sales, and I want to sort this data based on the region column, but I want this to be the criteria.

I want all the records for East, then all the records for West, then North, and then South. Now I cannot use the inbuilt A to Z or Z to A criteria because that will not give me this result, so I can create my own custom sorting criteria.

To do that, I’m going to select this data, then I’m going to go to the Data tab here and then click on the sort icon. When I do that, it opens the sort dialog box. Now here I’m going to first click on My Data Has Headers because I do not want my headers to be sorted. Now I’m going to click on Sort by and select Region, which is the column based on which I want to sort the data, and then in order, I’m going to click on Custom List.

When I do that, it opens the Custom Lists dialog box, and here I can create the list entry that would then be added as the custom sorting criteria. You can see it already has some of the criteria that are already pre-built in Excel, and I can add my own.

Here I can type East, then hit enter to go to the next line, then West, then North, and then South. Now when I click Add, it adds this as one of the criteria. So this is selected; I can now click OK.

When I do that, you can see the order is now East, West, North, South. And if you click on this drop down, you will notice that it gives me it in both directions, so East, West, North, South, and then South, North, West, East. So let me select this one, click OK, and as soon as I do this, you can see my data is sorted where all the records for East are together and then West, then North, and then South.

You can also import these criteria. If you have, say, a list in a column in Excel, then you can also import that into the custom list here. So let me show you how to do that. I’m going to go to the File tab here, then click on Options, and within the advanced options, I would scroll down to go to the General category options.

So here I have General Options, and now if I click on Edit Custom Lists, then you’ll see I have one additional thing that I get in the same Custom List dialog box, which is this part here. So here it allows me to import a list. I can just click on this and then select the cells that have the list. Once it’s done, I can click on Import and it is going to import the list here.

As of now, I have already added it, so nothing would happen. But if you have a custom list that you want to put in the custom list dialog box, then you can also import it by going to the File tab and then going to the Excel Options.

Let me show you a very fast way to quickly delete all the hidden rows or all the hidden sheets in your Excel file. Here I have this file where I have some of the rows that are hidden and I also have a sheet that is hidden in this file.

Now, I'm going to go to the File tab here, then click on Info, and here within Check for Issues, I'm going to click on Inspect Document. When I click on Inspect Document, it opens the document inspector, and now it has all these options that are selected. So I'm going to click on Inspect, and when I do that, it is actually going back to the file, and it is checking everything.

You’ll see that it shows me some of the things that are there, such as hidden rows and columns and hidden worksheets. If I want to remove them, I can quickly do that. You can see it says the number of hidden rows found is five, and the number of hidden worksheets found is one, so I can click on Remove All, and all the hidden rows are removed.

I can click on Remove All here for hidden worksheets, and all the hidden worksheets would be removed. So I can click on Remove All here, and then when I click Reinspect, if you want, you can reinspect it again, and you’ll see there are no hidden rows or hidden sheets.

So now when I go back, you can see now there are no hidden rows. Now, one thing that this tool is not doing is it is not deleting hidden columns, although it says that it is going to find hidden columns. It only finds hidden rows and hidden sheets. So you can see here I have some columns that are hidden; it couldn't identify, and it did not delete it.

If you have hidden rows or you want to delete hidden sheets, then this can be a very fast way to do that.

If you use the Quick Access Toolbar to add options that are hidden in the ribbon or options that are not even in the ribbon, then you are going to love this trick.

As of now, you can see I have all these options in my Quick Access Toolbar, and if I want to let’s say run a macro, I can just come here and click on this macro icon, or if I want to insert a checkbox, I can come here and click on this checkbox, or if I want to open the Go To Special dialog box, I can click on this.

Now, this is anyway very easy, but another easier way to do this is by using a keyboard shortcut. So when you press the ALT key, you will notice that these numbers appear in the Quick Access Toolbar, and these numbers actually become the keyboard shortcut for that icon. For example, for the checkbox, the number here is six, which means that if I want to insert a checkbox, instead of going to the Quick Access Toolbar using my mouse, I can just use ALT + 6.

So press the ALT key, then leave it and then press the 6 key, and when you do that, it inserts the checkbox based on the position of that icon in the Quick Access Toolbar. So if I want to open the Go To Special dialog box, it would be ALT + 7.

You can rearrange these icons; the numbers would accordingly rearrange. If you have more than nine icons, then the keyboard shortcut is going to change. So here, till 9 it is going to be ALT + 2, ALT + 3, ALT + 4, and so on. But when you have the 10th, it becomes ALT + 09 and ALT + 08, and so on.

Here’s a trick that can be useful when you are writing formulas. If you’re new to Excel and you’re learning formulas, or if you’re working with formulas that you have never worked before, then this trick can be useful for you.

Let’s say I’m using the XLOOKUP formula, so I type XLOOKUP, and it shows me all the functions that match this, which in this case, is only XLOOKUP. I hit the tab key, so it inserts the entire name of the function and it shows me this bracket and the cursor after the bracket. Now, if I need more help on the function, I can use the keyboard shortcut Ctrl + A.

So I hold the Control key and press the A key, and it is going to open the Function Arguments, where I get a lot more help about this function. It shows me all the arguments it takes; it tells me what kind of argument it takes. For example, the Lookup Value could be anything, but the Lookup Array needs to be a reference. You can enter everything here; it will also show you the formula result here, and it gives you a lot more context.

If you need even more help on this function, then you can click on Help on this Function, and it is going to open a web page where you can see examples of this.

Another thing which might not be useful in all the cases, but is interesting to know, is you can also use this keyboard shortcut where I have this function and I have these arguments. You can see all these arguments below in this bar in this tooltip. But if you also want these arguments in the cell, you can use the keyboard shortcut Ctrl + Shift + A.

When I use this keyboard shortcut, it inserts all the arguments right here. So if you are documenting your work, then this is something where you can instantly get the function along with all the arguments.

Here is a cool trick I learned from this book called Igniting Excel, which is written by Bill Jelen, who’s also known as Mr. Excel, and it’s a great book. It has a lot of tricks and tips, so if you want, you can get a copy of it.

Now, the trick here is that I have this data, and I do not want anyone to insert or delete a row in this data. So what I can do is I can go to a column to the far right, maybe to the end of it, and I can select these cells where I do not want anyone to insert or delete a row.

Then go to the formula bar, and here I’m to type =1, and you can enter any number. You can have =2 or =3, any number. And then I’m going to type Ctrl + Shift + Enter, and when I do that, it is going to convert this into an array.

So if I select this cell, you can see that it has put curly brackets around =1. But what it also does is, if I come back to my dataset here and I try and delete any of these rows, it is not going to allow me to do that. It says you cannot change part of an array because I have an array at the last column, so it doesn’t allow me to delete a row or insert a row.

If I try to insert a row, it would tell me you cannot change part of an array. This is a nice cool trick if you want to preserve your dataset and do not want anyone to insert or delete rows.

Here is a cool Excel trick I use almost every day. So here I have this data, and let’s say I want to move this column, which is for March, to the beginning. I want it next to the names column.

Now, the usual way of doing it would be I would copy this column, then I would come here, insert a column here, insert the copied column, then I would come back here and delete it. But here is a faster way. So you select this column. Now you can select the range or you can select the entire column. So in this case, let me select the range.

Then hold the Shift key and then bring your cursor to the edge of the selection, and when you do that, you will notice that your cursor changes into this four-pointed arrow. Now press the left mouse key and drag it to the left, and while you are doing it, you will see this thick green line appear.

Now because I want my data next to the names column, I would bring this green thick line here just next to the names column. And now when I leave my mouse key, you’ll see that the column has rearranged and the March column is now at the beginning, and the rest of the two columns have automatically shifted to the right.

So it’s just a one-step process rather than copy-pasting it, and you can do it for rows and columns as well. For example, let’s say I want the record for Lisa to be at the top. So I would select this, hold the Shift key, bring the cursor to the edge; it changes into this four-pointed arrow, and I would drag this to the top.

I would bring it where I want this data. When I see the thick green line just below the names header column, I would just leave the left mouse key, and it moves the record for Lisa at the top, and everything goes down.

Now, this is a great trick because when I'm doing it, it is not actually impacting any cells which are to the right or to my dataset or below my dataset. If I’m selecting this and I’m moving this row only, it’s not going to impact any cells that are here.

So this is a great trick, and it's a great way to move rows and columns.

Here I have the start date and the end date, and I want to calculate the total number of working days. Now there are multiple formulas that can do this in Excel, but let’s say I want to calculate the total number of workdays while not counting Saturdays and Sundays.

So you can do that by using a formula called NETWORKDAYS, where you can specify the start date, the end date, and in case you have a list of holidays, you can specify that so it will not count the holidays.

So in this case, I'm not going to use holidays just to keep it simple, and now when I hit enter, it gives me 32, which means that these are the total number of working days which only include Monday to Friday; Saturday and Sunday are not counted. But what if your weekend dates are not Saturday and Sunday?

What if it is, let’s say, Friday or Saturday, or if it is only Sunday? In that case, you can use this newer function called NETWORKDAYS.INTL. So NETWORKDAYS.INTL takes the start date, the end date, and it also gives you many options where you can choose a combination of weekend days. By default, it is Saturday and Sunday, but you can choose Sunday and Monday as weekends or Monday and Tuesday, and so on.

You can also choose single day weekends, so for example, if I choose Friday and Saturday, I can choose 7 here and hit enter. It tells me that there are 32 number of workdays when I include Friday and Saturday as the weekend days. And in case I only want Sunday as the weekend, then I can choose this; so 11, and it tells me that there are going to be a total of 38 working days when only Sunday is the day off.

But what if you are in a job where your workdays are, let’s say, Tuesdays and Thursdays? In that case, you can again use this function with a trick. Here I have NETWORKDAYS.INTL. This is my start date, this is my end date, and here instead of choosing from this, I'm going to manually enter the number of days that are working days for me.

So here I’m going to use a string of 0 and 1. If I enter one in all these cases, let’s say I enter 1 seven times, all of these are non-working days, which means all the days are weekends. Now if I hit enter, it gives me zero, but if my working days are, let’s say, Tuesday and Thursday, I would change the second one to zero.

A zero means a working day, and one means a non-working day. And this as zero, so now Monday, Tuesday is a working day, and Thursday is a working day, so Monday is off, Wednesday is off, and the rest of these days are off: Tuesday and Thursday are working. Now when I hit enter, it gives me 13, which means that there are 13 working days between these two days where Tuesday and Thursday are the working days.

So how can you also use this? In case you want to find out the total number of Fridays or total number of Mondays between these dates, then you can change this here.

So I would have 1,1,1,1,1,1,1, and if I want to count the total number of Mondays, I would change this first one to zero. And now when I hit enter, it gives me six, which means that there are a total of six Mondays between these two dates.

If you want to get the total number of Fridays, change this and make the fifth one as zero. So this is a great trick when you want to count the number of working days between two dates when you have these conditions.

Let me show you two new improvements that have come to Excel that are useful, and this is available only in the newer versions of Excel. So I'm assuming that when you’re using older versions, this might not be available to you.

So the first thing is when you are unhiding sheets, it wouldn't allow you to unhide all the sheets in one go. For example, in this case, I have four sheets that are hidden. Now, if I'm unhiding them, I can right-click on the sheet name, then go to the unhide option, and it shows me this unhide dialog box.

Now earlier you could only unhide sheets one by one, but now you can hold the Control key and select multiple sheets that you want to unhide. Now when I click OK, it is going to unhide all the sheets in one go.

The other thing is if you have made a selection earlier, you could not deselect some cells, but now you can. In this case, I've selected this range and I want to deselect, let's say, this record, then I can hold the Control key and deselect it, and it is going to remove it from my selection.

So here I have this workbook, and I want to hide these two sheets, Index1 and Index2. Now the way I want to do it is that no one can unhide them. Now, if I use the regular method, so I come here, I select these two sheets, right-click, and then click on hide, anyone can unhide them.

I can come here to the Data tab and right-click, go to unhide, and then I can select these two sheets to unhide them. So this method is not going to work, so I would have to use another way to hide them in such a way that no one can unhide them using the interface, and I'm going to do that using a very simple VBA property pin.

So what I'm going to do is go to the Developer tab and then click on the Visual Basic icon. When I do that, it opens the Visual Basic editor. You can also use the keyboard shortcut ALT + F11. So hold the ALT key and press the F11 key, and that is also going to open the Visual Basic editor.

Now here, if you do not see anything, click on View and then click on Project Explorer, and when you do that, it is going to show you all the objects of all the open workbooks. Here you can see I have Sheet1, Sheet2, and Sheet3—all these sheets.

Now, I’m also going to go to the View tab and click on Properties Window. When I do that, it opens this properties window. Now when I select a sheet, the properties of that sheet would be visible to me, and I'm going to make one change here for the sheets that I want to hide.

In this case, I want to keep the data sheet visible, but I want to hide the Index1 sheet. So I'm going to select it then go to the Properties pane here, and for this, I want to choose Excel Sheet Very Hidden.

So usually, from the interface, you can either hide a sheet or unhide a sheet, but with VBA, there is a setting called Very Hidden. If I select this and then go to Index2 and do the same thing—so come here and choose Excel Sheet Very Hidden—once I have done that, I can go back to my workbook, and you can see those sheets are now no longer visible.

If I come here and click on this option, you can see the unhide option is not visible to me, which means that I cannot unhide these sheets from here. If I want to unhide these sheets, I would have to go back to the VB editor and then change the property.

So I select Index1, and here it says Sheet Very Hidden; I will have to make the sheet visible.

Thank you for watching! That’s it for this video. I hope you found these Excel tricks useful. Also, if you're liking these videos, please subscribe to this YouTube channel and click on the Bell icon so that you never miss out on any new Excel tips video I come up with.

Thank you, and have a nice day!