Copy and paste conditional formatting to other cells
※ Download: Conditional formatting pada excel
Semua conditional formatting ini menyesuaikan ketika data berubah. Terdengar meremehkan bukan, tapi ini akan bermanfaat ketika didunia kerja.
Set icon mencakup panah untuk menunjukkan perubahan di dalam data, juga icon lainnya seperti bendera dan bar untuk menunjukkan data di dalam cell. Jika kondisi yang anda terapkan salah, maka range tersebut akan diformat berdasarkan kondisi salah. Text that Contains Digunakan untuk menampilkan format data yang sama dengan kata yang anda masukan.
Copy and paste conditional formatting to other cells - Ini merupakan cara yang bagus untuk menyajikan data secara visual.
In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. Note: This list is also available as a , and you can xls and xlsx format that includes examples of the techniques described here. For example, Figure A shows a simple timekeeping sheet that highlights a workday that's greater than eight hours. Because your organization requires approval for anything over an eight-hour day. Figure A Alert users to overtime hours requiring approval. Working with the time values complicates things a bit, as you can see in Figure B. Or you could use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0. Figure B This formula returns True when a time value is greater than eight hours. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Figure C shows an icon solution for the same rule violation in 1. Figure C Use icons instead of traditional formats to represent conditional rules. First, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Figure D shows the final settings click Reverse Icon Order first. Figure D These settings display green and red icons to alert users to an overtime violation. When this is the case, conditional formats can distinguish records. Figure E shows a data set of products with a conditional format highlighting only Condiment records. Figure E You can distinguish records based on a single value. Select the entire data range not the column headings so Excel can format the entire record row. Figure F shows the formula-based settings. Figure F You can highlight a row by applying the conditional format to the entire data range instead of a single column. For instance, suppose you want users to choose the category on the fly, as shown in Figure G. First, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot, as shown in Figure H. Then, use the Data Validation feature to create a list, also shown in Figure H. Figure G Let users choose the highlighted category from a validation list. Figure H You'll need a unique list and a validation list. With the list in place, update the conditional format formula to reference the input list cell, as shown in Figure I. Instead of referencing a cell within the row, the formula references the validation list in B2. Selecting an item from the validation list updates the conditional formatting. Figure I This rule references the validation list instead of a literal string value. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it's time to reorder, as shown in Figure J. Select the values you want to format — in this case, that's B2:B46. You could highlight the entire row or one of the inventory values. Then, apply the format shown in Figure K. Figure J Highlighted items that are running low. Figure K This rule highlights the product in column A when the in stock value goes below the reorder level. This rule, shown in Figure M, compares each value in column A to its counterpart in column B. If they're not the same, Excel highlights the value in column A. To highlight the values in column B instead, select the values in column B and update the rule formula to reference the values in column A. Figure L This rule highlights values in column A when they differ from the values in column B. Figure M Apply this rule to compare the two side-by-side lists. The Table feature offers several predefined formats that include bands, but you end up with a table instead of a plain data set, and that might not be what you want. When you don't want a table, use conditional formatting to create alternating bands, as shown in Figure N. The rule shown in Figure O highlights cells to achieve the alternate band effect. Figure N Banding can improve readability, but you'll probably want to highlight even or odd rows and not both, as shown here. Figure O Apply either of these rules to create an alternate band effect. For instance, the sheet in Figure P shows duplicate values in a single column. Select the values you want to format and apply the formula-based rule shown in Figure Q. Figure P Highlight duplicate values for a quick alert. Figure Q This rule highlights all occurrences of the same value. You can also hide duplicates which I don't always recommend by selecting a font color that matches the sheet's background. Figure R The concatenating formula in column C finds duplicates across multiple columns. You can use Excel 2007 and 2010's built-in Find Duplicates rule, but it has a few limitations. First, you don't have much flexibility with the actual formats. Second, this rule selects all duplicate values; you can't choose to ignore the first occurrence and highlight just the subsequent values, as this more flexible formula-based rule can. Figure S shows a common accounting tool known as cross-footing — the process of double-checking totals by comparing subtotals across rows and columns — in cell F16. Adding the conditional format makes the discrepancy hard to miss when the two totaling values don't match. Select either of the cross-foot formulas and apply the rule shown in Figure T. Figure S Combine conditional formatting with cross-footing for a hard-to-miss alert. Figure T This rule compares two values. As you can see in Figure V, the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5. Figure U This option finds the smallest or largest value in a range. Figure V A more complex rule highlights the smallest or largest duplicate value in a range. This rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule. The rule shown in Figure W will highlight the value 3 in the range named List only if 3 is also a duplicate. To find the largest duplicate value, substitute the MIN function with MAX. Figure W This rule ignores the smallest value unless it's also a duplicate value.
Ya, salah satu manfaat dari Menu Conditional Formatting ini adalah kita dapat mewarnai cell Excel secara otomatis jika teks tertentu diketikkan pada cell tersebut. Klik Conditional Formatting Cell yang ada pada Menu Home, grup Styles. When this is the case, conditional formats can distinguish records. The rule shown in Figure O highlights cells to achieve the alternate band effect. Di dalam contoh di atas, hasil penjualan terbaik memiliki background hijau tua, sementara nilai terendah dalam warna merah. Atau dalam tabel pegawai, format data yang jabatannya Manager, dsb.
