Create Names From Selection

Creating Defined Names enables you to take advantage of a host of excel features and shortcuts. This post shows one easy method to create Defined Names from your selection and showcases a nifty method to find where two Defined names intersect.

This tip involves naming cells, also known as Defined Names. Among the many benefits, Defined Names can be used to:

  • quickly navigate large spreadsheets
  • easily define print areas
  • simplify formula entry

In the below table you want to easily reference the data in any given column or row. Since the data is in a table you can easily create a Defined Name, for each column and row, using the Create from Selection command.

CreateNamesIntersect

Creating Defined Names From Selection

  1. Select all the cells in the table.
    Tip: select one cell, then press CTRL + A.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
  3. Check Top row and Left column.
  4. OK.

To display the Defined Names, press F3 or click the Name Box drop-down. Defined Names will also appear as you enter formulas, preceded by the name tag iconNametag.

 

PowerTip: The intersection of two Defined Names can be displayed using a formula.

Using Defined Names to Display the Intersecting Value

  1. In a blank cell, type =.
  2. Type the first Defined Name followed by a space.
  3. Type the second Defined Name and press Enter.

Cheers!

hɔuᴉnb

 
Watch this ~1m video to learn more.

Previous post and additional reading:

Column Select

Looking for an easy way to highlight a column that is not in a table? Use COLUMN SELECT

Note: This tip works equally well when editing an email in Outlook.

Looking for an easy way to highlight a column that is not in a table? Use COLUMN SELECT

ColumnSelect
Click to enlarge

Column Select

  1. Click insertion point at begining of text
  2. Press ALT as you CLICK + DRAG to end point.

Once selected the text can be formatted or deleted. The selection collapses after your executed command.

Be smooth:  ALT+CLICK instruction brings up the Thesaurus*, so be don’t click quickly. Column Select (ALT + CLICK + DRAG) works best to when you use a smooth, paint-like motion (more like Pollock than Seurat)

* (a deadly neolithic creature hellbent on correcting ingesting your text and regurgitating its own).

Watch this 1m video for more.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

Related:

A revolutionary drone-based delivery network is being tested—in Bhutan

While Amazon’s drone plans stall, Silicon Valley startup, Matternet, is piloting (pun intended) a drone service in Bhutan. This aerobo (aerial-robotic) fleet is providing a means of delivering medicinal supplies to rural communities.

Display Multiple Time Zones

Negotiating meeting schedules between time zones can be tricky. Here are two simple methods to help keep track of time in other regions:

Add Additional Time Zone to the Outlook Calendar

DualTimeZones

  1. In Outlook, click File, then select Options. The Outlook Options dialog appears.
  2. At left, select Calendar, then scroll down to the Time zones section.
  3. In the Label area type a brief description for the local time zone.
  4. Check Show a second time zone.
  5. In the second Label area, type a brief description for the second time zone.
  6. Select additional time zone from the second Time zone drop down.
  7. OK.
OutlookOptionsCalendar
Outlook Calendar Options

To Add Time zones to Windows Taskbar 

The windows taskbar has the ability to show two additional time zones.

TimeClocks
Windows 8 Time Display
  1. RIGHT + CLICK on time displayed in lower right corner of taskbar.
  2. Select Adjust date/time.
  3. Click Additional Clocks tab.
  4. Check Show this clock.
  5. For each additional time zone, check Show this clock, select time zone from the drop-down, then enter a name in the field below.
  6. OK.
Additional Clocks dialog
Additional Clocks dialog (CLICK to enlarge)

 

For additional multi-regional scheduling resources, visit the World Clock website. There you will find meeting calculators, daylight savings information and interactive maps.

Additional reading..

Cheers!

hɔuᴉnb

More Windows Key Shortcuts

computer_key_Windows 

Here are two favorite shortcuts of mine. Both feature the underutilized Windows Key .

To Open an Explorer Window

  • Hold the Windows Key and press ‘E‘.

This combination offers a fast(er) way to get to your files and folders.

Explorer

To Open a ‘Run’ window

  • Hold the Windows Key and press ‘R‘.

To launch a program or access a file (provided you have the program name or file path), use this Windows Key combo.

Notepad

 

 

Cheers!
hɔuᴉnb

WinEvolution

Comments and questions are always welcome!

Additional recommended reading:

Automating the Spreadsheet using Check Boxes, DSUM & IF

In this post, we put this altogether with an interactive report. Using check boxes and DSUM the user will select what criteria to sum by.

4 of 4: Automating the Spreadsheet using Check Boxes , If Statements, and DSUM

In the previous posts we added Check Boxes to our spreadsheet, linked Check Boxes to cells, and created a conditional summation using DSUM. In this post, we put this altogether with an interactive report. Using Check Boxes, IF statements, and DSUM, the user will select what criteria to sum by.
Dsum_CheckBox
Click to enlarge

In the example depicted above, we sum the values in the Amount column based upon the criteria entered in column G.

Watch this ~3m video to learn more.

 

Adding and Linking the Check Boxes

DSUM_Checkbox Properties Linked Cell

  1. Insert two blank rows above the database.
  2. In the second blank row, add Check Box Form controls for each criteria value to be included in the summary. In the example depicted, we created Check Boxes for each region (see Adding a Check Box for instructions).
  3. Link the Check Box to the cell above: Right + Click on the Check Box, select Format Control, and enter above cell in the Cell link field (for more detailed instructions click here ). Repeat for each Check Box.

 

Creating the Criteria Column

DSUM_Checkbox Function dialog

We will use the IF function to copy the Checked labels into the criteria range

  1. In a blank column, enter the field name into the cell (i.e., ‘Region’) .
  2. Move cursor to the cell immediately below.
  3. On the Formula tab, in the Function Library group, click Logical and select IF.
  4. In the  Logical_test field, select or type Linked cell above the Check Box. In the example depicted below enter A3 to test Checked state for ‘North America.’
  5. In the Value_if_true field, select or type the Check Box cell. In the example depicted below enter A4 for the text ‘North America.’
  6. In the Value_if_false field, type ” – ” (dash in quotes).
  7. Click OK.

Repeat steps 2 through 7 for each Check Box value to be included in the summation.

To  test the IF statements, check and uncheck the boxes. When checked, the text associated with that Check Box will appear in the criteria column.

 

Inserting the DSUM function

DSUM_Checkbox Properties DSUM

  1. Select the cell to contain summation (i.e., F5).
  2. On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
  3. In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
  4. Click OK. The Function Arguments dialog appears.
  5. In the Database area, enter either the saved name or cell reference for the table (e.g., FoodInventory or A7:F255).
  6. In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
  7. In the Criteria area, type or select the cells that contain the criteria; this includes the column heading(s) and value to search for (i.e., G4:G8).
  8. Press OK.

Tip: Hide the criteria column to make the report appear less cluttered.

That’s it. Now, to add a region to the summation, check that region’s Check Box. To remove a region from the summation, uncheck it.

 

Cheers!

hɔuᴉnb

 

Previous post and additional reading:

Comments and questions are always welcome!

Using DSUM

3 of 4: Summing values using DSUM

(revised and expanded from an earlier post)
In the previous posts we added Check Boxes to our spreadsheet and linked those Check Boxes to cells to display True when checked and False when unchecked. In this post, we take a brief break from Check Boxes to explore the DSUM function. But don't fret, in upcoming post we will tie Check Boxes and DSUM together to produce an interactive report.
Click to enlarge

DSUM, short for Database Summation, enables you to summarize data in a table (aka, database) based upon a specific condition (aka, criteria). Similar to the SUMIF function, DSUM can employ Boolean (i.e., AND/OR) logic in its criteria.

=DSUM( database, field, criteria)

  • Database: the entire table, including row values, and headings
  • Field: the column containing numbers to sum
  • Criteria: condition to test for inclusion in the summary (contains field headings and value)

In the example depicted above, we sum the values in the Amount column based upon the criteria entered in B3:B4 (i.e., Region: Europe)

Watch this ~3m video to learn more.

 

Tip: when working with large data, name the cells to simplify formula entry.

To Name a Group of Cells

  1. Select all the cells.
    Tip: select one cell, then press CTRL + A to select all contiguous (touching) cells
  2. Click in the Name box, type name (e.g., “FoodInventory”) and press Enter.
    Note: Names cannot begin with a number, or include spaces.

Creating the Summation using DSUM

  1. Type criteria, entering the field names on one row and the condition to test right below it
    (i.e., In B3 enter Region and in B4 enter Europe).
  2. Select the cell to contain summation (i.e., A4).
  3. On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
  4. In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
  5. Click OK. The Function Arguments dialog appears.
  6. In the Database area type the name of database (i.e., FoodInventory).
    Tip: press F3 key to display list of named cells and select  from that list.
  7. In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
    Alternatively, you can enter the number of the summation column, counting from left of the table (i.e., 5).
  8. In the Criteria area, type or select the cells that contain the criteria; this includes the column heading(s) and value to search for (i.e., B3:B4).
  9. Press OK.

Next and  Final: Automating the spreadsheet using DSUM and Check Boxes.

Cheers!

hɔuᴉnb

 
Previous post and additional reading:

Comments and questions are always welcome!