Excel 2024: More Excel Tips

More than 200 ideas were sent in for this book. While the following ideas did not get much press here, they are self-explanatory in a 140-character tweet.

• To make Excel open full-screen, right-click the Excel icon and change the Run setting to Maximized. -David Ringstrom, CPA
• Use a thin light border line to create useful scrollbar maximums when your worksheet contains charts that the scroll bars don't recognize. -Chris Mack
• Highlight duplicate records with Conditional Formatting, Highlight Cells, Duplicate Records. -@Leaf_xl
• Color every other row with this conditional formatting formula: =MOD(ROW(),2)=0. -Pedro Millers
• Have a photo appear after a dropdown list in Excel -Michael A. Rempel
• Use pictographs for charts (column and pie): Copy picture: select series, paste. -Olga Kryuchkova
• For a pie chart with too many slices: move small slices to second pie using Pie of Pie chart. -Olga Kryuchkova
• Use X/Y charts for drawing artwork. -Joerg Decker
• INDEX can return an entire row/column by using 0 for column/row. -Sumit Bansal
• CHOOSECOLS with negative instance number replaces INDEX but counts from the end. Two get the 2nd to last word from A1, use =CHOOSECOLS(TEXTSPLIT(A1," "),-2). - Diarmuid Early
• Put an apostrophe in front of an Excel formula to stop it from being evaluated. -@DiffEngineX
• DATEDIF(A2,B2,"Y")&" yrs, "&DATEDIF(A2,B2,"YM")&" mos, "&DATEDIF(A2,B2,"MD")&" days." -Paul Wright
• Insert rows without breaking formulas. Cell above is OFFSET(thisCell,-1,0) - Jon Wittwer, Vertex42.com
• Subtract 1 from NPV function to get the Net Present Value of the investment. -Olen L. Greer
• Use EDATE to move the date out one month or year. -Justin Fishman
• Find mystery links in the Name Manager. Ta-da! -Lisa Burkett
• Formulas created in Notepad, saved as CSV, & opened in Excel work. Example: mike,=proper(A1) will give Mike. -@mdhExcel
• Double-click a formula. Excel color codes the cells referenced in the formula. -Cat Parkinson
• Turn off Edit Directly in Cell. Then double-click a formula to show cells used in that formula, even if in external workbook. -Sean Blessitt and David Ringstrom
• Go To Special, Constants helps spot constants within a block of formulas where a formula is overwritten with a number. -@HowToExcel
• Select a random 5% of data using =RAND()<.05. -Olga Kryuchkova
• Mark formulas with Conditional Formatting formula =HASFORMULA(A1). -Justin Fishman
• Double-click a number in a pivot to get the detail behind that number. -@Sheet1
• Array formula to count without COUNT: =SUM(IF(ISNUMBER(MyRange),1,0)). -Meni Porat
• In VBA, use Range("A1").CurrentRegion instead of RANGE(). It is like pressing Ctrl+*. -Arnout Brandt
• You can use hyperlinks to launch VBA macros. Smaller than buttons. -Cecelia Rieb
• Use a macro to color the heading cells that have filters applied. -Peter Edwards
• Use Environ("UserName") in VBA code for restricting workbook access. -Angelina Teneva
• Use a UDF in a hyperlink to change cells mrx.cl/udfhyperlink -Jordan Goldmeier
• There are a variety of games written in Excel (2048, MissileCommand, pleuroku, TowerDefense, Pac-Man, Rubic's Cube, Yahtzee, Tetris). -Olga Kryuchkova
• POINT mode in Excel lets you build a formula using arrow keys to point to cells or ranges. If this stops working, see if you inadvertently pressed ScrLk key. You will see SCROLL LOCK near left side of Status Bar. (ScrLk is near PrtScrn and Break keys). -Vijay Krishnan