You are reading the article How To Lock Cells In Microsoft Excel updated in February 2024 on the website Tai-facebook.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 How To Lock Cells In Microsoft Excel
Do you want to lock cells in Excel to prevent unwanted changes in an Excel sheet? Here’s how to lock cells and protect sheets in Microsoft Excel.
Tip: also learn how to use Power Query and Power Pivot in Microsoft Excel.How to Lock All Cells in a Sheet
When creating a new sheet, all cells are automatically locked. However, there are cases when the last user keeps the cells unlocked manually. Here’s how to lock all cells in a sheet:
Open the Excel workbook containing the cells you want to lock.
Reenter the unprotect password.How to Unlock All Cells in Excel
Follow these steps to unlock all locked cells in a sheet:
Enter the unprotect sheet password when prompted.
Extra: Master VLOOKUP guide for Microsoft Excel here.How to Lock Specific Cells in Excel
You can lock specific cells in Excel and keep other cells unlocked in the same sheet by following these steps:
Highlight the cells you want to lock.
Reenter the unprotect password.How to Unlock Specific Cells in Excel
If you need to change specific cells that are locked, follow the instructions below to unlock them:
Enter the unprotect sheet password.
Highlight the specific cells you want to unlock.
- What You Can and Can’t Do when Locking Cells in Excel
What you can do:
Lock formulas in cells
Choose specific cells to lock and leave unselected cells unlocked
Protect your sheet and workbook with a password
Lock symbols in cells
What you can’t do:
Lock cells in the Web version of Microsoft Excel
Edit locked cells in a protected sheetHow to Protect a Workbook in Excel
You can lock an Excel workbook to protect it from the Insert, Delete, Rename, Move, Copy, Hide, and Unhide commands. Here’s how to protect a workbook:
Reenter the password.Frequently Asked Questions What will happen when I unprotect a protected sheet?
Once you enter the password for a protected sheet, you will need to create a new password or use the same password as before to protect it again with a password.Is it necessary to enable Protect Sheet after locking cells?
Yes, as locked cells can still be edited when unprotected. Therefore, locking cells with unprotected sheet is useless.
All screenshots by Natalie dela Vega
Natalie dela Vega
Natalie is a writer specializing in tech how-tos and gaming. When she’s not writing, she plays PC games and travels. Here at MakeTechEasier, you will see her write about guides, tips, and solutions for Windows and iOS.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
You're reading How To Lock Cells In Microsoft Excel
In this post, we will show you how to count colored cells in Microsoft Excel.
While working with Excel, we often color-code cells to distinguish them from the rest of the data. This is good because the only concern is to grab someone’s attention. But when we color-code cells to indicate some value (for example, ‘green’ for approved and ‘red’ for not approved), we may require counting these values at some later point in time.
Excel offers plenty of functions, but sadly, none of them can be used directly to calculate the count of color-coded cells. Also, there’s no built-in way (whatsoever) to do so in Excel. However, there are workarounds to count colored cells in Microsoft Excel, which we will discuss in this post. So keep reading!How to count Colored Cells in Excel?
To count colored cells in Microsoft Excel, you may use the Find and Select tool or the Excel Filter and SUBTOTAL() function. Let us see both these methods in detail.1] Use the Find and Select tool
The Find & Select tool is used to find and select cells that meet specific requirements. It lets you execute the Find command, Find and Replace command, and Go To commands in Excel. With the help of the Find command, you can count the number of cells that have a specific background color. Here’s how:
Let’s say we have a worksheet wherein we have some sample data as shown in the above image. The data lists some blog posts that have either been scheduled or published on TheWindowsClub. Here, yellow color has been used to indicate that a post is scheduled, and green color has been used to indicate that a post is published. For this data, we can count the total number of scheduled posts as follows:
As you select the option, the Find and Replace dialogue box will disappear and your mouse pointer will turn into a plus symbol with a color picker next to it.
The dialogue box will further expand and show the list of cells that are color-coded in yellow. At the bottom of the dialogue box, you will see the count of these cells.2] Use Excel Filter and SUBTOTAL() function
The SUBTOTAL() function can be used to perform a calculation on a given range of cells. It can be used to apply other functions on the cell range while ignoring the cells that shouldn’t be part of the calculation. A total of 11 functions are supported by the SUBTOTAL() function.
It has the following syntax:SUBTOTAL(function_num,ref1,[ref2],...)
function_num is the numeric reference to the function that should be used in the calculation. It takes either 1 to 11 or 101 to 111 as value. For example, 1 or 101 for AVERAGE(), 2 or 102 for COUNT(), 3 or 103 for COUNTA(), etc. The variation in the numeric reference determines whether to include(1-11) or exclude(101-111) hidden cell values in the function.
ref1 refers to the cell range whose subtotal is required.
ref2 refers to another cell range for the calculation. This may go up to ref254.
Taking the same example as above, we can count the total number of scheduled posts using the SUBTOTAL() function as follow:
Place your cursor in cell A11 and type the following function in the Formula Bar on top:=SUBTOTAL(103,A2:A10)
Here, value ‘103’ indicates that the COUNTA() function has been referred to in the SUBTOTAL() function. Also, it indicates that hidden values will be excluded, if any. If you have numeric data in your worksheet, you may refer to the COUNT() function (2/102).
Since there are no hidden rows at this moment, the count comes out to be 9. When you manually hide rows with green cells, the SUBTOTAL() function automatically shows the updated results (8, 7, etc.).
If the data is quite large (which is obvious), manually hiding rows could be tedious. So you can add a filter to show only yellow-colored cells in the data range.
This will filter out yellow-colored cells. The result of the SUBTOTAL() function will also update based on the visible data.
Note: When SUBTOTAL() function is used on filtered data, hidden values are always ignored regardless of the function_num argument.
So this is how you can count colored cells in Microsoft Excel. I hope you find this useful.
Also Read: How to highlight Cell or Row with Checkbox in Excel.Can Excel count by colored cells?
There’s no built-in way to count colored cells in Excel, but there’s a trick. You can apply a color filter to the cell values and then count only visible cells by passing the COUNT() or COUNTA() function as an argument in the SUBTOTAL() function. The SUBTOTAL() function can be used to perform various arithmetic operations (sum, count, average, etc.) on a given cell range.Can you use Countif with colors?
Read Next: How to create a dropdown list with color in Excel and Google Sheets.
Watch Video – Fill Down in Excel
A lot of times, you may encounter a data set where only one cell is filled with data and the cells below it are blank till the next value.
Something as shown below:
While this format works for some people, the problem with this sort of data is that you cannot use it to create Pivot Tables or use it in calculations.
And this has an easy fix!
In this tutorial, I will show you how to quickly fill down cells in Excel until the next filled value.
You can easily do this using a simple Go-To special dialog box technique, VBA, or Power Query.
So let’s get started!
Suppose you have a data set as shown below and you want to fill down data in column A and column B.
In column B, the aim is to fill ‘Printer’ till the last empty cell below it, and then when ‘Scanner’ starts, then fill ‘Scanner’ in the cells below till the cells are empty.
Below the steps to use go to special to select all the blank cells and fill down using a simple formula:
Select the data in which you want to fill down (A1:D21 in our example)
Go to the ‘Home’ tab
The above steps would select all the blank cells in the data set (as shown below).
In the blank cells that are selected, you would notice that one cell is lighter than the rest of the selection. This cell is the active cell where we’re going to enter a formula.
Don’t worry about where the cell is in the selection, as our method would work in any case.
Now, below are the steps to fill down the data in the selected blank cells:
Hit the equal-to (=) key on your keyboard. This will insert an equal to sign in the active cell
Press the up arrow key. This will insert the cell reference of the cell above the active cell. For example, in our case, B3 is the active cell and when we do these two steps, it enters =B2 in the cell
Hold the Control key, and press the Enter key
The above steps would automatically insert all the blank cells with the value above them.
While this may look like too many steps, once you get the hang of this method, you’ll be able to quickly fill-down data in Excel within a few seconds.
Now, there are two more things that you need to take care of when using this method.
The first one is to make sure that you convert formulas to values (so that you have static values and things don’t mess up in case you change data in the future).
Below is a video that will show you how to quickly convert formulas to values in Excel.
In case you’re using dates in your data (as I am in the example data), you would notice that the filled-down values in the date column are numbers and not dates.
If the output in the date column are in the desired date format, you’re good and don’t need to do anything else.
But if these are not in the correct date format, you need to make a minor adjustment. While you have the right values, you just need to change the format so that it is displayed as a date in the cell.
Below are the steps to do this:
If you need to do this fill-down once in a while, I recommend you use this Go-To special and formula technique.
Although it has a few steps, it’s simple and gives you the result right there in your dataset.
But in case you have to do this quite often, then I suggest you look at the VBA and the Power Query methods covered next
You can use a really simple VBA macro code to quickly fill down cells in Excel till the last empty value.
You only need to add the VBA code to the file once and then you can easily reuse the code multiple times in the same workbook or even multiple workbooks on your system.
Below is the VBA code that will go through each cell in the selection and fill down all the cells that are blank:
Sub FillDown() For Each cell In Selection If cell = “” Then cell.FillDown End If Next End Sub
The above code uses a For loop to go through each cell in the selection.
Within the For loop, I have used an If-Then condition that checks whether the cell is empty or not.
In case the cell is empty, it is filled with the value from the cell above, and in case it is not empty, then the for loop simply ignores the cell and moves to the next one.
Now that you have the VBA code, let me show you where to put this code in Excel:
Copy-paste the VBA code into the code window
The above steps would run the VBA code and your data would be filled down.
If you want to use this VBA code again in the future, you need to save this file as a macro-enabled Excel workbook (with a .XLSM extension)
You can also add this macro to the Personal Macro Workbook and then use it in any workbook on your system.
Using Power Query is recommended when you’re using it anyway to transform your data or combine data from multiple worksheets or multiple workbooks.
As a part of your existing workflow, you can use the fill-down option in Power Query to quickly fill the blank cells.
To use Power Query, it’s recommended that your data is in an Excel Table format. If you can’t convert your data into an Excel table, you will have to create a named range for the data and then use that named range in Power Query.
Below the steps to use Power Query to fill down data till the next value:
While this method may sound a bit of an overkill, one great benefit of using Power Query is that it allows you to quickly update the resulting data in case your original data changes.
While the Power Query method works well, it’s best to use it when you’re already using Power Query in your workflow.
If you just have a dataset where you want to fill down the blank cells, then using the Go To special method or the VBA method (covered above) would be more convenient.
So these are three simple ways that you can use to fill down blank cells until the next value in Excel.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
What is Mean in Excel?
In Excel, the mean is also called the average. It’s a function that calculates the arithmetic mean, which means finding the average of a set of numbers. The average function adds all the values of a given range and divides the sum by the total count of cells.
In this article, we will learn How to Find Mean in Excel. For example, if you want to calculate the mean for 5, 10, 15, 20. The average function will add all the values (5 +10+15+20) and divide the sum by the total number of cells, i.e., 4. The result will be 12.5.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
The AVERAGE function is useful to calculate the average marks of students, the average salary of employees, and the average sales produced over time. This article explains how to find mean in Excel with five different methods and examples.
number1-It is a mandatory argument. It can be a number, range, or cell reference
[number2],..-It is an optional argument in the function.How to Find Mean in Excel?
Select an empty cell to display the result
Enter the formula =AVERAGE(cell range). For instance: =AVERAGE(A5:A20)
The formula returns the arithmetic mean.
You can download this How to Find Mean Excel Template here – How to Find Mean Excel TemplateExample #1: How to Calculate Arithmetic Mean in Excel?
Step 1: Select “Cell C2” and enter the formula:
Note: In the formula =AVERAGE(A2:A11), we have selected the range from A2 to A11. You can also type individual numbers in the formula.
Step 2: Press “Enter”.
Result: The formula will return 27.5.Alternate Method:
Excel has an in-built function to calculate the average values in a continuous row or column. You can follow the following steps.
Step 3: Press “Enter”
Result: The formula will display 27.5 in Cell A12.Example #2: How to Find the Mean of a Data Set?
Step 2: Select “Cell F2” and enter the formula:
Step 4: Drag the cell downwards.
Result: The formula will return average marks for respective students, as shown below.
)Example #3: Using AVERAGEIF for a Specific Condition
The AVERAGEIF function calculates the mean or average of a given data that meet certain criteria.
Step 1: Select “Cell C11” and enter the formula:
Step 3: Select “Cell C12” and enter the below formula:
Step 4: Press “Enter”.
Result: The formula return the average of total sales for only sugar.
Note: You can see the difference between the AVERAGE and AVERAGEIF function results. The AVERAGE function has considered values from C2 to C10. The AVERAGEIF function has considered only Cell C4, C6, C8, and C9 values, representing sugar.Example #4: Using AVERAGEIFS for Multiple Conditions
In the earlier example, we calculated the average of total sugar sales. Let’s say you want to calculate the average total sugar sales exceeding $40. You have to use the AVERAGEIFS function to accomplish this task.
Step 1: Select “Cell C11” and enter the formula:
Step 2: Press “Enter”.
Result: The formula returns the average value of total sugar sales exceeding $40.Example #5: Using AVERAGEA
Points to remember while using the AVERAGEA function:
Text strings are evaluated as
Boolean or Logical value like TRUE is considered 1 and FALSE as 0.
Empty text (“”) in the cell is also evaluated as 0.
Step 1: Select “Cell C12” and enter the formula:
The formula will return 25.47777778 in Cell C12, as shown below.
Result: The formula will return the average of total sales
Step 3: Select “Cell C13” and enter the formula:
Here, the AVERAGEA function has taken the Cell C5 and Cell C10 values (Text-Non Sold) as 0 and divided the sum by 9, i.e., the total count of cells in the data.
Here, the AVERAGE function ignores Cell C5 and Cell C10 values and divides the sum by 7.Things to Remember on How to Find Mean in Excel
A user can give 255 input arguments in the AVERAGE function, which includes numbers, text strings, arrays, cell references, and logical values (TRUE or FALSE).
If any cells do not meet the above criteria, Excel will display a #DIV01 error.
The AVERAGEA function will evaluate 1 for TRUE and 0 for FALSE values.
To calculate the mean with specific conditions, use AVERAGEIF or AVERAGEIFS.Recommended Articles
This article provides a step-by-step tutorial on how to find mean in Excel using various functions like AVERAGE, AVEARGEIF, AVEARGEIFS, and AVERAGEA with practical examples. To understand more about Excel features, you can also read our other articles.
The Lock Screen in Windows 11 not only greets you with lovely wallpaper but also serves as a security mechanism to keep unauthorized users from accessing your computer. By default, you must go through the lock screen every time you boot up or sign in to your desktop. Some people appreciate the idea of a lock screen so much that they customize it.
However, other users simply want to get to their desktop faster and prefer the lock screen to be deleted. Windows 11 lacks direct access to disable the lock screen. You can’t disable the lock screen with a toggle or anything similar because it’s part of the Windows operating system. But don’t worry; this post will show you three ways to disable the lock screen on your Windows 11 PC.
Disable Lock Screen in Windows 11
The lock screen on Windows 11 is lovely, but is it possible to turn off the lock screen when you don’t need it? Here’s exactly how you can disable the lock screen on Windows 11.
1. Using Group Policy
The Group Policy Editor is a crucial tool for Windows administrators. You can change the requirements for passwords, startup programs, and other features. GPE is only available for Windows Professional, Ultimate, and Enterprise. Here’s how to use Group Policy to disable the lock screen on Windows 11:
Step 1: To open the Run command box on your computer, press Win + R.
Step 2: To open the Group Policy Editor, type gpedit.msc and press Enter.
Step 5: A new window will pop up with the option’s detailed details. By default, the enabled option is selected, indicating that the lock screen is activated.
Step 6: To turn off your system’s lock screen, select the Disabled button.
Step 8: Then, restart your computer. You will note that there is no lock screen and that you are immediately taken to the login screen.
2. Using Registry Editor
You can also disable Windows’ lock screen by modifying registry settings. However, for backup purposes, export a copy of your registry. In the event that the registry becomes corrupted, it will allow you to restore the prior settings. Here’s how you can get rid of the lock screen using the registry:
Step 1: To open the Run command box, use the Win key + R. To open the Registry Editor, type regedit in the text input box and press the enter key.
Step 2: Then, in the text input area, paste the following path and press the enter key: ComputerHKEY LOCAL MACHINESOFTWAREPoliciesMicrosoftWindows
3. Using a Third-party Application.
Step 1: Run the setup for WinAeroTweaker.
Step 2: Then, follow the on-screen steps to customise the app to your settings.
Step 3: Then activate it.
Step 4: Under Boot and Logon, select Disable Lock Screen.
Step 5: On your right, there’s an option as Disable Lock Screen. Check to enable it.
Step 6: When prompted, restart your computer.
Even if you aren’t prompted, we recommend restarting your computer. The lock screen on your Windows 11 system should be disabled after your PC restarts.
If you’re hoping for a more seamless experience while entering Windows and attempting to disable the lock screen, there are a few more steps you may take.
Disable Sign-in Password Requirement
When your PC wakes up from sleep, this option disables the sign-in requirement. To make the process easier, follow the instructions below.
Step 1: First press Windows + I or launch Settings
Step 2: Then, select Sign-in options from the menu.
Step 3: Toggle the drop-down menu next to When should Windows prompt you to sign in again if you’ve been away? and then choose Never.
Step 4: Your system’s sign-in requirements will now be disabled.
Disable Sign-in Requirement
All users on the existing system will no longer be required to use a password. This will make it much easier for you to get past the log-on page. To make the process easier, follow the instructions below:
Step 1: To open the Run dialogue box, press Windows + R on your keyboard.
Step 3: Remove the check mark from the box for Users must enter a user name and password to use this computer.
If your Windows 11 is waking up from sleep or waking up after a power cycle, restart it for good measure. You should no longer be required to sign in.
Frequently Asked Questions
Why Disable Lock Screen in Windows 11?
The first obstacle to going to the desktop is the Windows lock screen. It has a background image, the current date and time, and app notifications on it. However, if you don’t want to see any of those things, turning them off is a good idea. You can go straight to the desktop after removing the lock screen and entering your PIN or password. For a public computer, you can also set up a lock screen-free PC.
Note that turning off the lock screen does not turn off the login screen. To access the system, you must still enter your PIN/password. To get to the desktop faster (and if privacy isn’t an issue), you can also remove the PIN/password required on the login screen. You now know the benefits and drawbacks of turning off the lock screen. Here are three simple ways to turn off the lock screen in Windows 11.Is it a good idea to disable the lock screen on Windows 11?
Regardless of your needs, it is not suggested that you disable your lock screen. Since their introduction, modern computer gadgets have come a long way and are now an integral part of our daily lives. You may or may not be aware of the sensitive data saved on your device, but deactivating the lock screen puts that data in danger.
By turning off the lock screen, unauthorised users will be able to gain access to your computer and install dangerous software, jeopardising your privacy and security. As a result, if you don’t have a pressing need to disable the lock screen, you shouldn’t do so for the sake of your device’s security and privacy.
It takes some time to become used to the Group Policy Editor. Furthermore, it is not available to Windows Home customers. So, if you want to disable the lock screen on Windows 11, you can use the registry modification. However, Winaero Tweaker is a free utility that allows you to adjust Windows through a graphical user interface.
iPadOS 17 comes with a number of new features, and everyone’s attention was drawn to the revamped Lock Screen. The Lock Screen on your iPad can now be uniquely customized like never before. And if that’s not enough, you can create multiple Lock Screens, choose from a wide variety of wallpaper options and collections, or even add specific focus modes.
Additionally, similar to what Apple did with the iOS 16 Lock Screen, you can add widgets and alter the font style and font color. Feeling overwhelmed? Don’t. Here’s a complete guide that will help you easily customize your Lock Screen with iPadOS 17.
How to set up new Lock Screen on iPad
Unlock your screen → Tap and hold an empty area on the Lock Screen.
Learn more about adding wallpapers on iPadOS 17 in the following sections.Add a Lock Screen wallpaper
You can pick a new Wallpaper from one of these sections: Featured, Suggested Photos, Weather & Astronomy, Kaleidoscope, Emoji, Unity, Pride, Collections, Color.
Once you choose a section, you’ll be presented with various options.
Create Emoji wallpaper for Lock Screen on iPad
Tap and hold an empty space on Lock Screen.
Swipe right to left to head to the Add New page → Hit the (+) Add button.
Now, you can swipe across the screen to choose from these different tones: Dynamic, Grid, Large, Radial, Spiral.
Additionally, you are also allowed to change the background color of the Lock Screen wallpaper.
Tap the color circle in the bottom right corner of the screen.
Choose the color you want for the background.Add a plain color as your iPad Lock Screen
Choose your preferred color and close the window.Set a photo as your Lock Screen wallpaper
Swipe across the screen to view filters. The available filter options are
The available filter options are as bellow: Natural, Black & White, Duotone, Color Wash.
Certain images will let you use more than the 4 regular shades. It is dependent on the angle of the image or background noise.
If you want to use all the filter options, I recommend using a photo taken from a good distance and with minimal background noise.Use Photo Shuffle for iPadOS 17 Lock Screen
The Photo Shuffle feature in iPadOS 17 makes it simpler to change the Lock Screen wallpaper automatically.
Go to the Add New Wallpaper page → Tap Photo Shuffle.
Moreover, you can also Choose Photos Manually if you prefer.
Tap Add → Choose Set as Wallpaper Pair to finish the process.How to crop a Lock Screen wallpaper
Now, pinch in or out on the wallpaper of your choice to adjust.
Additionally, you can swipe left or right on the screen to change the filter.
How to switch Lock Screens on iPad
Press and hold the Lock Screen.
Swipe across the screen to view the Lock Screens you have created.
Customize clock in iPad Lock Screen
Unlock your iPad → Press and hold the Lock Screen → Select Customize.
Tap the clock widget.
When you’re done customizing, tap anywhere on the screen → Tap Done.
Additionally, on the Lock Screen, you can also see live activities such as the song currently playing and more.
Add widgets to iPadOS 17 Lock Screen
Unlock your iPad → Press and hold the Lock Screen.
Select Customize → Choose Lock Screen.
From the left pane, choose the app you wish to add the widget for.
Additionally, you can also tap the date widget above the time widget to change it.
Once done, tap anywhere on the screen.
Enable Depth Effect in iPadOS 17 Lock Screen
Depth Effect is a feature on iPadOS 17 Lock Screen that separates the wallpaper into layers and positions the clock accordingly. It is best suited for Photos. However, remember that not all images support Depth Effect on iPad.
By default, this feature is turned on for Photos, but you can check if it’s enabled by following these steps:
Go to the Customize screen → Choose Lock Screen.
Tap the three dots at the bottom right.
How to set different wallpapers for iPad Lock Screen and Home Screen
Tap and hold the Lock Screen → Select Customize.
You have different options to choose from:
Pair: It uses the same wallpaper for the Lock Screen and Home Screen.
Color: It uses the most dominant color from an image. You can further customize it by tapping the (+) plus icon and selecting your preferred color.
Gradient: It gives a common gradient to the image. To customize it further, tap the plus icon and choose the gradient you like.
Photos: You can choose a photo of your choice for the Home Screen.
Blur: It blurs the existing wallpaper image.
How to delete a Lock Screen wallpaper in iPadOS 17
If you didn’t enjoy the Lock Screen and want to remove it, follow the steps described below.
Press and hold the Lock Screen.
Swipe upwards on the wallpaper you wish to remove → Tap the Delete icon.
By doing this, you will remove both the Lock Screen and the associated Home Screen for the wallpaper.
Keep the iPad display on for longer
Although iPadOS 17’s Lock Screen is fantastic, it can be inconvenient when your iPad frequently goes to sleep. So, here’s a solution for you.
Open the Settings app on your iPad → Tap Display & Brightness.
Choose the time frame from the options below.
Organize notifications on iPad
Open the Settings app on your iPad.
Go to the Notifications section.
Choose how you want your notifications to appear on the Lock Screen by selecting one of the below-mentioned.
Bhaskar is a member of the iGB family and enjoys experimenting with words and rhythms. He also has a knack for web and app development. If not writing, you may find him on strings or engaging in sports. And by evening, his quotes will be appearing on your Instagram feeds.
Update the detailed information about How To Lock Cells In Microsoft Excel on the Tai-facebook.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!