Cập nhật thông tin chi tiết về How To Allow Sorting And Filter Locked Cells In Protected Sheets? mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
How to allow sorting and Filter locked cells in protected sheets?
In general, the protected sheet cannot be edited, but in some cases, you may want to allow the other users to do sorting or filtering in the protect sheets, how can you handle it?
Allow sorting and filtering in a protected sheet
Allow sorting and filtering in a protected sheet
To allow sorting and filter in a protected sheet, you need these steps:
5. In the Protect Sheet dialog, type the password in the Password to unprotect sheet text box, and in Allow all users of this worksheet to list to check Sort and Use AutoFilter options. See screenshot:
Then the users can sort and filter in this protected sheet
Tip. If there are multiple sheets needed to protect and allow users to sort and filter, you can apply Protect Worksheet utility of Kutools for Excel to protect multiple sheets at one time.please go to free try Kutools for Excel first, and then go to apply the operation according below steps.
Now all specified sheets have been protected but allowed to sort and filter.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
complex formulas, charts
and anything that you have used before;
Create Mailing List
and send emails…
Super Formula Bar
(easily edit multiple lines of text and formula);
(easily read and edit large numbers of cells);
Paste to Filtered Range
without losing Data; Split Cells Content;
Combine Duplicate Rows/Columns
… Prevent Duplicate Cells;
Select Duplicate or Unique
Select Blank Rows
(all cells are empty);
Super Find and Fuzzy Find
in Many Workbooks; Random Select…
Multiple Cells without changing formula reference;
Auto Create References
to Multiple Sheets;
, Check Boxes and more…
, Add Text, Remove by Position,
; Create and Print Paging Subtotals;
Convert Between Cells Content and Comments
(save and apply filter schemes to other sheets);
by month/week/day, frequency and more;
by bold, italic…
Combine Workbooks and WorkSheets
; Merge Tables based on key columns;
Split Data into Multiple Sheets
Batch Convert xls, xlsx and PDF
More than 300 powerful features
. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More… Free Download… Purchase…
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint
, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by
Read More… Free Download… Purchase…
From this short tip you will learn how to quickly sort cells by background and font color in Excel 2016, Excel 2013 and Excel 2010 worksheets.
Last week we explored different ways to count and sum cells by color in Excel. If you’ve had a chance to read that article, you may wonder why we neglected to show how to filter and sort cells by color. The reason is that sorting by color in Excel requires a bit different technique, and this is exactly what we are doing to do right now.
Sort by cell color in Excel
Sorting Excel cells by colour is the easiest task compared to counting, summing and even filtering. Neither VBA code nor formulas are needed. We are simply going to use the Custom Sort feature available in all modern versions of Excel 2016, 2013, 2010 and 2007.
Select your table or a range of cells.
In the Sort dialog window, specify the following settings from left to right.
The column that you want to sort by (the Delivery column in our example)
To sort by Cell Color
Choose the color of cells that you want to be on top
Choose On Top position
In our table, the “Past Due” orders are on top, then come “Due in” rows, and finally the “Delivered” orders, exactly as we wanted them.
Tip: If your cells are colored with many different colors, it is not necessary to create a formatting rule for each and every one of them. You can create rules only for those colors that really matter for you, e.g. “Past due” items in our example and leave all other rows in the current order.
If your cells are colored with many different colors, it is not necessary to create a formatting rule for each and every one of them. You can create rules only for those colors that really matter for you, e.g. “Past due” items in our example and leave all other rows in the current order.
Sort cells by font color in Excel
If you want to sort by just one font color, then Excel’s AutoFilter option will work for you too:
Apart from arranging your cells by background colour and font color, there may a few more scenarios when sorting by color comes in very handy.
Sort by cell icons
For example, we can apply conditional formatting icons based on the number in the Qty. column, as shown in the screenshot below.
As you see, big orders with quantity more than 6 are labeled with red icons, medium size orders have yellow icons and small orders have green icons. If you want the most important orders to be on top of the list, use the Custom Sort feature in the same way as described earlier and choose to sort by Cell Icon.
It is enough to specify the order of two icons out of 3, and all the rows with green icons will get moved to the bottom of the table anyway.
How to filter cells by color in Excel
If you want to filter the rows in your worksheet by colors in a particular column, you can use the Filter by Color option available in Excel 2010, Excel 2013, and Excel 2016.
The limitation of this feature is that it allows filtering by one color at a time. If you want to filter your data by two or more colours, perform the following steps:
Create an additional column at the end of the table or next to the column that you want to filter by, let’s name it “Filter by color”.
Enter the formula =GetCellColor(F2) in cell 2 of the newly added “Filter by color” column, where F is the column congaing your colored cells that you want to filter by.
Copy the formula across the entire “Filter by color” column.
Apply Excel’s AutoFilter in the usual way and then select the needed colors in the drop-down list.
As a result, you will get the following table that displays only the rows with the two colors that you selected in the “Filter by color” column.
And this seems to be all for today, thank you for reading!
Most likely this is going to be my last article in this year, so let me take a moment and wish you Merry Christmas and a very Happy New Year. We will be delighted to welcome you again here on this blog in the year of 2014!
You may also be interested in
Setting Up Data
When you sort and conditionally format data, usually it’s a large number of rows. The following examples use a set of data with 20 rows. The data represents a list of customers and the amount of revenue made from each sale.
(Data setup for sorting and formatting examples)
Notice that headers are used at the top of each column. This is important for sorting when you want to change the column to sort on. Excel’s sorting functionality is handy even when you only have a few rows. If you want to view a list of revenue numbers based on the highest value or lowest value, instead of eyeing values and determining the right one based on your own human review, Excel 2019 will ensure that you can sort values and find the ones that have the highest revenue.
(Excel sort buttons)
Excel can identify if your data is a set of dates, textual values or numbers. The sort function then orders cells based on the detect data type. For instance, if you have a list of revenue sales, Excel knows to sort cells based on numeric values. If you have cells formatted as dates, Excel knows that these values should be ordered in chronological order. Cells that are text values such as customer names are ordered alphabetically.
(Sort configuration window)
The “Sort By” dropdown has the headers for each column listed. Since we have “Customer” and “Revenue” as a column header, these two values display in the “Sort By” dropdown. If you don’t have column headers, Excel lists the column letter labels. Should you have several columns, having only letter labels make it difficult to configure your sort order.
The “Sort On” dropdown defaults to “Cell Values,” which means that the value is used for the sort. This is the typical configurations, but you can also sort on cell color or font color. This is useful when you set conditional formatting, which is covered in the next section.
The “Order” dropdown indicates if you want to sort data in ascending or descending order. The “A to Z” option means that you want to sort data in ascending order. The “Z to A” option means that you want to sort data in descending order.
(Data sorted by “Revenue”)
Notice that names still match up with revenue values. This is because the “Sort” functionality knows to keep rows aligned even though you’re ordering data by one column. If you decide to change the order to customer names, repeat these steps and choose “Customer” from the “Sort By” dropdown. Columns are still aligned properly but rows are ordered again based on the customer’s name.
Sorting data doesn’t highlight certain cells that might need to stand out among the others. For instance, you might want to know which customers had revenue within a specified range. You might want to know which customers had revenue under or over a certain threshold. You can sift through all of your records, but conditional formatting that changes the font or background makes these cells stand out much more and makes them easier to find. With a short customer revenue list that contains only 20 rows, you can easily find the customers that bought and added revenue to your income, but if you had thousands of records even a sorted list would make it difficult to find specific records.
Excel has a function called “conditional formatting” that changes the color of a cell’s font or the background color of a cell to make it stand out and easy to find when you’re looking for certain values that meet a condition.
(Conditional Formatting button)
The “Conditional Formatting” button is found in the “Home” ribbon tab. The image above shows the Conditional Formatting button, which is also in the “Styles” category.
(Conditional formatting dropdown options)
With conditional formatting, you aren’t limited to just one color with one condition. You can set multiple colors using multiple conditions. For instance, you might want to know which customers brought in revenue under $100 and which customers brought in over $1000. You can then take this data and use it for reporting and product information. Using revenue charts and conditional formatting, you then know which customers are the best (or worst) to market to and upsell additional product.
From the “Highlight Cells Rules” dropdown options, choose the “Greater Than” option. This opens a new configuration window.
(Greater than conditional formatting configuration window)
(Conditional formatting set on cells greater than $1000)
With conditional formatting, you can now quickly see which customers brought in revenue over $1000. This formatting persists even when you sort cells again using the “Sort” option. Should you decide to use other conditions, you can make them other colors to make it easy to distinguish between the two conditions.
Once you understand the way conditional formatting and sorting works, you can make it much easier to work with large data sets that must be evaluated each month, especially revenue sheets.
As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.
Go down to the Sort option – when hovering over Sort the sub-menu will appear
Select Expand the selection
The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.
The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.
To do this using the filter you would:
Go to the Data tab on Excel ribbon
Select the Filter tool
Select Eastern Company from the dropdown menu
Select Dylan Rogers from the Salesperson dropdown menu
Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.
The Sort & Filter Tool
In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.
But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:
As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!
Use Learn Excel Now to help with all your Excel questions and training needs. We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.
Bạn đang xem bài viết How To Allow Sorting And Filter Locked Cells In Protected Sheets? trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!