Tag: Excel

Excel NCAA templates

March Madness is upon us!

In 1892 Coach James Naismith hammered two peach baskets into the gym balcony, and forever changed how we use Excel!

NCAA_LooneyToons

Using these free templates available in Excel, you can plot out your final four projections!

To access NCAA templates

  1. Click File and select New.
  2. In the Search Office Templates field type ‘NCAA‘ (no quotes) and press the search arrow. The search results will populate the dialog.
  3. Select preferred template and click Download.

ncaa

from Wikipedia:

[Naismith] divided his class of 18 into 2 teams of 9 players each and set about to teach them the basics of his new game of Basketball. The objective of the game was to throw the [ball], into the fruit baskets nailed to the lower railing of the gym balcony. Every time a point was scored, the game was halted so the janitor could bring out a ladder and retrieve the ball. Later, the bottoms of the fruit baskets were removed. The first public basketball game was played in Springfield, MA, on March 11, 1892. That day, he asked his class to play a match in the Armory Street court: 9 versus 9, using a soccer ball and two peach baskets.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Excel Indents

Excel Indents

Want to make your spreadsheet more legible? Indents, added where appropriate, will make your spreadsheet easier to mentally digest.
Sadly, it won’t make bad financial news easier to swallow.

Watch this 15 second video to learn the what you need to know.

Indent a cell

  1. Select the cell(s) to indent
  2. On the Home tab, in the Alignment Group, click the dialog launcher Dialog Launcher
    (or press CTRL + SHIFT + F to display the Font dialog and click the Alignment tab).
  3. In the Text alignment section, click the Horizontal drop-down and select Left (Indent).
  4. In the Indent field, type or select preferred indent increment (i.e., 1, 2).
  5. Click OK.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Excel Sparklines

Be Trendy!

Sparklines

Sparklines are an easy way to add visual trend-lines to your tabular data.
And what better way to distract your audience than by dangling something sparkly in front of them!

Watch this 60 second video to learn the what you need to know.

Inserting Sparklines to your Excel data

  1. On the Insert tab, in the Sparklines group, click Sparkline type (e.g., Line). The Create Sparklines dialog appears.
  2. In the Data Range field enter the values to be charted.
  3. In the Location Range field enter cell range where char is to be displayed.
  4. Click OK.

Tip: With an inserted sparkline selected, the Sparklines Tools: Design tab appears on the Ribbon. Use the tools on this tab to change colors and add markers.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Bubble Charts

Break out the Bubbly!

Its well past the new year so a little bubbly is long overdue. Bubble charts are great in that they allow you to graph in 3 dimensions: Length, Height and Breadth (or area). Although the steps below are written with Excel in mind, they can also be applied to PowerPoint.

Watch this 3½ minute video to learn the basics regarding interpreting and creating bubble charts.

Creating a Bubble Chart

  1. Input your data, placing the X-axis data in the first column, Y-axis data in the second column, and the area (bubble size) in the third column.
  2. Select the data.
  3. On the Insert tab, in the Chart group, Click XY and select from Bubble types. A Bubble chart is added to the worksheet

Note: If necessary click Switch Row/Column (on the Chart Tools:Design tab) to swap the series data.

Adding Data Labels

  1. CLICK on one of the bubbles in the series. The entire series becomes selected.
  2. RIGHT+CLICK  one of the selected bubbles and select Add Data Labels. Data labels will appear.
  3. To format label appearance and content, RIGHT+CLICK on a bubble and select Format Data Labels.

Formatting the Bubble Appearance (Fill)

  1. CLICK on one of the bubbles in the series. The entire series becomes selected..
  2. CLICK (again) on the bubble. The single bubble will be selected.
  3. RIGHT+CLICK on the selected bubble and select Format Data Point. The Format Data Point dialog/pane appears.
  4. Select Fill and set the fill options (e.g., color, pattern, fill, etc).
  5. Close.
  6. Repeat as necessary for remaining bubbles.

Cheers!
hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

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 subscribe to this blog and receive notifications of new posts by email.

Go back

Your message has been sent

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 subscribe to this blog and receive notifications of new posts by email.

Go back

Your message has been sent

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 subscribe to this blog and receive notifications of new posts by email.

Go back

Your message has been sent