Excel: Insert Tables From Web

Use the Insert from Web option, to quickly import tables from web pages into Excel.

Watch this 2 minute video to see all you need to know.


To Insert Data From Web into Excel

  1. Using your web-browser, locate the content to be imported.
  2. RIGHT + CLICK in the addressbar and select Copy.
  3. In Excel, on the Data tab of the Ribbon, click From Web. A New Web Query dialog will appear.
  4. RIGHT + CLICK in the addressbar, select Paste, and click Go. The source webpage will populate the dialog.
  5. Click the yellow arrow grnarrw next to the table(s) you wish to copy (the icon will change to a green check grnchk  ).
  6. Optionally, click Options and select formatting preference (i.e., None, Rich Text, HTML) then click OK.
  7. Click Import. The Import Data dialog will appear.
  8. Select starting cell to import data to or select “New Worksheet” to import into a new sheet.
  9. Click Properties, uncheck Save Query Definition*, and click OK.
    *Alternatively, to maintain a link to variable data, leave Save Query Definition checked; you will be prompted to ‘Enable Content’ each time the file is opened.
  10. Click OK to complete the import.
Additional reading..

Cheers!

hɔuᴉnb

Comments and questions are always welcome!

Enter your email address to follow this blog and receive notifications of new posts by email.

MS Office: Insert Screenshot

Need to insert a screenshot into your presentation, document, spreadsheet or email? The Office 2010/2013 Insert Screenshot button makes this easy.

Watch this 60 second video to see all you need to know.


To Insert a Screenshot

  1. Display content window that has the material to capture. Do not minimize this window.
  2. Open or switch to destination application (i.e., MS Word, PowerPoint, Excel, or Outlook).
  3. Place cursor where you wish to insert the screen capture.
  4. On the Insert tab of the Ribbon, click Screenshot button. The available (i.e. not minimized) windows will display as thumbnails.
    • To insert an entire window; select associated thumbnail from the drop-down.
    • To insert a portion of the window previously displayed; select Screen Clipping, then CLICK + DRAG cross-hair around the portion to insert.
Additional reading..

Cheers!

hɔuᴉnb

Comments and questions are always welcome!

Enter your email address to follow this blog and receive notifications of new posts by email.

Data Consolidation

Using Consolidate you can summarize data from separate worksheets onto one master sheet.  Data Consolidation performs a statistical function (e.g. Sum, Average) on a series of ranges (lists). And the best part:  the lists need not have identical content.

Watch this 2+ min. video to learn all you need to know.

Performing the Data Consolidation

Logo_Microsoft_Excel_2013 Click  here to open Toy List Consolidation.xlsx sample spreadsheet

Prep: Sort each list by the first column and remove all blank rows and columns within the lists.

Tip: Use Define Name to set name each list.

  1. On the summary sheet, select the upper-left cell where the consolidation is to appear.
  2. On the Data tab, in the Data Tools group, click Consolidate.
  3. In the Function box, select function (i.e., Sum).
  4. In the Reference field, type the name or cell references of first list (including column and row headings), then click Add. Repeat this step for each list to be summarized.
  5. Check Use labels in Top row and Use labels in Left column.
  6. Click OK.

Cheers!

hɔuᴉnb

Related reading:

Comments and questions are always welcome!

Enter your email address to follow this blog and receive notifications of new posts by email.

Excel 2013 new feature: Timeline Slicer

What’s New in Excel 2013?  Timeline Slicers !

Timeline Slicers are a feature new to Excel 2013.
If your PivotTable contains dates, a Timeline Slicer affords you and your reviewers an easy method to filter and scale of the dates displayed..

(For detailed instructions on creating PivotTables review the 5 minute video tutorial  PivotTables and Slicers )

Inserting the Timeline Slicer

  1. Click here to open the file PivotTable Advanced Functions.xls file.
  2. Review the Sales sheet. This is the source of our PivotTable data.
  3. Click the Summary Sheet. A PivotTable has already been inserted, with the dates of Weekly Sales displayed in Rows, and a Slicer for the Category field applied.
  4. Select one of the cells containing data (e.g. A4).
  5. On the Ribbon, on the PIVOTTABLE TOOLS: ANALYZE tab, in the Filters group, click Insert Timeline. The Insert Timelines dialog appears.
  6. Check Weekly Sales and click OK. The Weekly Sales Timeline Slicer appears.
askq_timeslicers2
Weekly Sales Timeline Slicer

Working with Timeline Slicers

  • CLICK or CLICK+ DRAG on the timescale to filter timeline.
  • To change the scale, click the Level drop-down, at right, and select Years, Quarters, Months, or Days.
  • To quickly format, select a style from  the Timeline Styles on the TIMELINE TOOLS: OPTIONS  tab of the Ribbon.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

Excel Advanced Filter

   

So you’ve mastered the FILTER tool (aka, ‘Filter-in-place’). Are you ready to step up to ADVANCED FILTERS?

Although the Filter-in-place tool is easy to use, it does not handle multiple column INCLUSIVE criteria very well.

Using the ADVANCED FILTER you can:

  • Copy filtered records to another part of the spreadsheet, leaving the original database undisturbed.
  • Generate a BOOLEAN OR (aka, inclusive) condition across multiple fields.

What does that that last part mean? Let’s take the following scenario.

The last time Russia played host to the Winter Games was in 1994.  In preparing for the 2014 Olympics , you are tasked to create of separate list that includes:

  • ALL RUSSIAN MEDAL WINNERS (any year), and
  • 1994 GOLD MEDALISTS, in the WINTER OLYMPICS, of any nationality.

An Olympic feat (pun intended)? Not for ADVANCED FILTER!

(cue the Olympic music

Performing the ADVANCED FILTER requires a little preparation. You will need to prepare a CRITERIA RANGE to ask the question, and an OUTPUT RANGE to display the result. These ranges must be be in a separate area of the same worksheet  preferably to the right of the original.

Click here to download the 2014 Olympic Data file, then execute the instructions below.

Preparing the Criteria and Output Ranges

  1. Select and copy the column headings in cells A1:G1.
  2. Paste the headings, starting in cell J1 and again in cell J6.
  3. In cell L2 type ‘RUS’
  4. On the following row, in the appropriate cells, type ‘1994‘ ( J3), ‘Gold‘ (O3) and ‘Winter‘ (P3).
Preparing the Criteria and Output ranges
(click to zoom)

Performing the Advanced Filter

  1. Place cursor on one of the cells from the original database (e.g. B4).
  2. On the the Data tab of the Ribbon, in the Sort and Filter group, click Advanced. The Advanced Filter dialog will appear.
  3. For Action select Copy to another location.
  4. Review the List Range. If necessary correct the reference to include the source data.
  5. In the Criteria Range field type or select cells J1:P3.
  6. In the Copy To field, type or select the Output Range header, cells J6:P6.
  7. Click OK.

Voilà! The records that satisfy these criteria are copied to the Output range, and you get a virtual laurel wreath!

Closing Ceremony Notes:

  • Our criteria range included the headings and two additional rows. Each OR (inclusive) condition is entered on a separate row. Although our criteria uses data from 4 columns, there are only two separate, inclusive criteria; (1) RUSSIAN OR (2) GOLD MEDALIST in YEAR 1994. Were additional inclusive criteria necessary (e.g., Gender)  the criteria range in Step 5 would need to be increased, accordingly.
  • The copied data is not linked to the original. Edits to the original source data do not affect the copied output. Should you edit the source data you need only re-run the Advance Filter to synchronize data.
  • Do not put anything below the Output Range headers that you intend to keep; Excel erases all the rows beneath prior creating the output.

PivotTables & Slicers

Want a quick introduction to PivotTables? Watch this 10 minute video. I demonstrate the basic features of PivotTables and feature Slicers, new to Excel 2010. And for more, view the earlier post: PivotTable Intro, Step by Step.

Excel Speaks!

Here’s another cool feature of Excel: Speak Cells on Enter.

Speak Cells on Enter

This can prove valuable as a means to verify accurate data entry. The only setup required is to add a button to your Quick Action Toolbar (QAT).

To setup:

  1. RIGHT + CLICK on the QAT and select Customize Quick Access Toolbar.
  2. Set the Choose commands from drop-down to All commands, then scroll down and select Speak Cells on Enter.
  3. Click Add, then OK.

The button now appears on your QAT. Click button to toggle the feature on or off.

When active, each time you enter in a cell, the cell contents will be read back to you. Unlike other reader programs this voice is clear and rather pleasant (take note Acrobat).

Now if only you could select the voice,  I’d take something along the lines of a HAL 9000, or K.I.T.T. model.

Additional voice features of Excel include:

  • Speak Cells
  • Speak Cells – Stop Speaking Cells
  • Speak Cells by Columns
  • Speak Cells by Rows
  • Stop Listening to Voices in My Head*

*available only to select consumers. What, I am not one of them? I am so! You keep out of this.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!