Learn Excel Blog

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.

QuickTipScreenClipping

 

Here are some important steps:

1) Go to the web page that has the picture you want to copy.

2) Make sure the picture is visible behind the Excel screen.

3) When you choose Screen Clipping, Excel will hide, you will see the windows behind Excel. That screen will “dim” out. Take the mouse and draw a rectangle of the area you want to insert into Excel.

Why is Screen Clipping better than using the Available Windows?  With the Available Windows, you get the complete window, the toolbars, the menu, the address bar, the footer. You will then have to crop the excess out using the crop tool.

This trick is also awesome for getting Excel charts into Power Point!

Excel Filter by Selection

               

There is a great Filter by Selection icon in Access. Excel offers the same functionality, but it is hidden and mislabeled!

FilterBySelection

To add the command to your Quick Access Toolbar:

  1. Right click the QAT
  2. Choose Customize Quick Access Toolbar
  3. From the top left dropdown, change from Popular Commands to All Commands
  4. In the left listbox, select AutoFilter.
  5. In the center, click the Add >> button.
  6. Click OK

To use the command, select one cell in your data. For example, choose a cell in the customer column with Ford. Click the Funnel icon in the QAT. Excel will turn on the Filter dropdowns and limit the customer column to just Ford. No need to open the dropdown, uncheck (Select All), then find Ford.

Tip: To further filter by other columns, click a value (such as East in the Region column) and click the Funnel icon again.

 

Convert Excel Dates to Month Names

               

Need to Subtotal or Group by Month?

DateToMonthName

The TEXT function will take any number or date and convert it to text using a particular custom number format.

Try these:

  • =TEXT(A2,”MMMM”) for full month name
  • =TEXT(A2,”MMM”) for 3-letter month abbreviation
  • =TEXT(A2,”DDDD”) for full month name
  • =TEXT(A2,”DDD”) for 3-letter month abbreviation

You can use any valid custom number format, even the ones with three zones for positive, negative, and zero. If the custom number format already contains a quotation mark, change it to two quotation marks:

  • =TEXT(B4,”””Please remit “”$#,##0.00;””Credit balance of “”$#,##0.00;””Zero Balance”””)

 

Excel Fill Alphabet or Roman Numerals

               

Sure, Excel can fill January, February, March, but what about A, B, C or I, II, III?

QuickTipsFillABC

For the upper case alphabet, use a formula of =CHAR(ROW(65:65)) and copy down.

For the lower case alphabet, use =CHAR(ROW(97:97))

For upper case Roman numerals: =ROMAN(ROW(1:1))

For lower case Roman numerals =LOWER(ROMAN(ROW(1:1)))

Tip: After filling any of these, copy and Paste Values so the list does not change when someone inserts rows in the 65-91 range.

Note: Custom lists could also solve this problem, but that is a Quick Tip for another day. (If you can’t wait, turn to page 79 in the book).

 

Excel Search Entire Workbook

               

You need to find something, but you don’t know what worksheet it is on.

QuickTipFindInAll

 

Display Find with Ctrl+F

Click the Options > > button.

Open the Search dropdown and change from Sheet to Workbook.

Learn Excel from MrExcel