Set the format to fill the cells with a color, depending on the application I use either a faint off-white to down play the color or a bright yellow to really make it pop - the choice is yours. Note: RangeAddress should be absolute("$A$1:$A$20"), and FirstCellAddress should be relative ("A1"). Type the following formula in the text box: =AND(COUNTIF(" RangeAddress", " FirstCellAddress")>1, MATCH(" FirstCellAddress"," RangeAddress", 0) ROW()) making sure to substitute accordingly. > Use a formula to determine which cells to format Select the range from which you wish to filter out duplicate text entriesĬlick on Conditional Formatting > New Rule. The method detailed below will allow you to filter our duplicate entries based on a conditional format. You can filter duplicate text entries as described above if you use the advanced filter option, however it will not allow you to do an additional filter on an adjacent column afterwards without duplicating the data first. So what do you say now? Isn’t Excel Exciting? □ Alternatively learn how to create org charts in excel. Insert a quick organization chartĬlick on menu > insert > diagram to open the above dialog, just select the organization chart option, enter node values and you have a pretty organization chart. Group them with similar colors if you have lot of sheets, it looks nice. Right click on sheet and select “Tab color” option to change the worksheet tab colors. Change the color of various sheet name tabs You can do the same way for a horizontal split as well □ġ4. Just click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. Split sheets in to two, compare side by side to be more productive if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes. Select the cell diagonally beneath the row / columns you want to freeze (for eg. Freeze Rows / Columns in your sheet, Show important info even when scrolling In the above sample you can see the number of working days between New years day and September first of this year (labor day). Just type =networkdays(start date, end date, list of holidays) to fetch the number of working days. If you work on project plans, gantt charts alot, this can be totally handy. Find total working days between any two dates, including holidays Width is returned as the nearest integer. Just use =cell("width") to find the width of the column to which that formula cell belongs. Find width of a column with formula, really! Alternatively you can use Alt+= keyboard shortcut. Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range. Remove conditional formatting / all formatting with one click You can “repeat columns while printing” as well from the same menu. When you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. Repeat rows on top when printing, show table headers on every page You can fetch company profiles in the same way. Just enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. Fetch live stock quotes / company research with one click You can even add a shadow effect from here. Just right click on the chart, select format chart option, in the dialog, check the “rounded borders”. Add rounded border to your charts, make them look smooth You can also change the color of grid line from here (not recommended) 5. Go to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. Just change the sort orientation from “sort options” in the data > sort menu. What if your data flows from left to right instead of top to bottom. Select the data, go to data > filter > advanced filter and check the “unique items” option. Just select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone □ Change the shape / color of cell comments Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |