Excel Quick Tips

Make Font Red If It Meets a Condition – Without Conditional Formatting

               

Before there was conditional formatting, there was custom number formatting. You can still add a condition to your custom number formats. Select the range of cells. Press Ctrl+1 to open the Format Cells dialog. Select the Number tab. Choose Custom from the bottom of the list. In the Type box, enter a format such as(…)

Data Pasted to Excel Starts Splitting at Comma?

               

          Usually, you grab text from Notepad or Email or a web page, paste to Excel, and it all stays in one column. But…one day, late in the afternoon, the pasted data starts automatically parsing into separate columns!     Here is the deal… I bet you did a Text to(…)

Prevent #N/A! in Excel VLOOKUP

               

So, you have this nice little =VLOOKUP(A2,$Z$2:$AB$99,3,False) formula. But in order to prevent the #N/A! error when something is not found, you used to have to do the VLOOKUP twice:  =IF(ISNA(VLOOKUP(A2,$Z$2:$AB$99,3,False)),”Not Found”,VLOOKUP(A2,$Z$2:$AB$99,3,False)). This is far simpler starting in Excel 2010: Think about it. Let’s assume you are doing 1000 VLOOKUPs. There are 990 that are(…)

Add Any Excel Icon to the QAT

               

Have you found a great Excel feature that is buried three clicks deep? Make it always be 1 click away by adding it to the Quick Access Toolbar (QAT) Adding to the QAT is simple: Right-click the icon and choose Add to Quick Access Toolbar.  

Excel Screen Clipping Tool

               

If you need to insert part of a web page … or anything… into Excel. This feature is new in Excel 2010. Go to Insert, Screenshot. Ignore all of the window icons, but go to the bottom and choose Screen Clipping.   Here are some important steps: 1) Go to the web page that has the picture(…)

Learn Excel from MrExcel