MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: More Excel Tips

January 04, 2021 - by

Excel More Excel Tips. Photo Credit: Sam Truong Dan at Unsplash.com

I published more than 200 ideas since February. 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 Full Screen. -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 http://t.co/TjbAtSkJ3t
  • 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 and return a cell reference. -Sumit Bansal
  • 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

Title Photo: Sam Truong Dan at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.