Unofficial Excel Stuff

Date and Time Entry

2018-04-29T03:01:00.000-07:00

Month Day, Day Month

QDE An Excel Date Entry Add-In
Ron de Bruin

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic

See all Topics excel

Week Numbers

2018-04-20T03:25:00.000-07:00

Who's counting?

For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.

2004 had 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then
datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.

See all Topics excel

Default Save

2018-04-14T03:47:00.000-07:00

Choose your own location

When you choose to save most Office files, the Save dialog box defaults to the Documents or My Documents folder.

(The following directions work in 2007+, but you need to click on the Office button in the upper left corner of the Window)

Word: you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.
Excel: Tools>Options. On the "General" tab change the default location.
PowerPoint: uses Tools>Options and the "Save" tab.
Access: Tools>Options and the "General" tab for Databases and Projects
Publisher: Tools>Options "General".
Outlook: will make you take an underground tour into the Registry to change the location to save e-mail attachments.
FrontPage/Expression Web: appears to require the same sort of spelunking.

Change the folder where e-mail messages and attachments are saved

If you don't want to change the default, but would like to be able to quickly go to an alternate site, open the Save or Save Attachment dialog box. On the left side of the box is the Places Navigation bar. If you click the Desktop icon, that location will be used to save the file.

You can add spots to the bar. Browse to the specific folder. Highlight the folder and click the down arrow beside the Tools option. Select "Add to My Places."

The file or e-mail attachment can then be saved where you want.

See all Topics excel

Accustom Yourself to Excel

2018-04-08T03:01:00.000-07:00

Shake hands with a worksheet

How to get used to the new user interface in Excel 20013.

Add a Get Started tab to the Ribbon
Take advantage of the Quick Access Toolbar
Minimize the Ribbon, maximize your workspace
Choose your own default font
Restore missing worksheet tabs
Freeze panes here, there, and everywhere

Surviving the switch to Excel 20013

See all Topics excel

Camera Tool

2018-04-04T03:16:00.000-07:00

Smile!

(This is the pre-2007 routine. For 2007+ see the end. )

To create a linked picture of part of a spreadsheet for use elsewhere:

Edit>Copy the Cell or Range
Choose the target cell
Hold down the Shift key and choose Edit>Paste Picture Link

If you're going to do this frequently, go to Tools>Customize. Choose the Commands tab and locate Camera in the Tools category. Drag to place the Camera button on your toolbar.

If the Camera tool is on your toolbar:

Select the Cell/Range
Click the Camera tool
Move the cursor to the target position and click to insert the linked picture

To create a static picture from cells, or a chart to use in another part of your workbook, or another program:

Clear cell gridlines if you do not want them displayed in your picture.
(Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")
On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
Hold down SHIFT and click Copy Picture on the Edit menu
(For the best picture quality, make sure As shown on screen and Picture are selected)
Click OK
Select the worksheet or other document where you want to paste the picture
Edit>Paste.

You can use the Picture toolbar to change the image. (to display the toolbar, right-click any toolbar and choose Picture) To paste information you've copied from another program as a picture in Microsoft Excel, hold down SHIFT and click Paste Picture or Paste Picture Link on the Edit menu. You can, also, create a dynamic linked text box by using the tool on the Drawing toolbar to place the object on the worksheet.
Now click to the Formula box. Type = and the click on the cell you wish to link.

Also see:
Copy - Paste Methodology
by Jon Peltier

The process is a little different in 2007+. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options. Choose copy as Picture. Next pick a location and choose Paste as picture.

See all Topics excel

OLAP Cubes

2018-03-28T03:50:00.000-07:00

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

Wikipedia:
OLAP

See all Topics excel

VBA Named Arguments

2018-03-22T03:54:00.000-07:00

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:

MsgBox "Hello World!", , "My Message Box"

Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

(To find out a function's named arguments, select the function in your code and press [F1].)

See all Topics excel

Zero 0

2018-03-20T03:58:00.000-07:00

Zero is nothing

If a zero isn't worth anything, why show it?

Here is a Microsoft tutorial about how to deal with zilch:

Display or hide all zero values on a worksheet
Use a number format to hide zero values in selected cells
Use a conditional format to hide zero values returned by a formula
Use a formula to display zeros as a blanks or dashes
Hide zero values in a PivotTable report

Hide Zeros

See all Topics excel

What's New in Excel 2013

2018-03-16T03:48:00.000-07:00

Different can be good

Microsoft has a information that explains the highlights of the new features of Excel:

Improved ribbon
Access workbooks in new ways
Make fast, effective comparisons from lists of data
Sparklines
Create workbooks with more visual impact
Collaborate on workbooks in new ways

There are also other links.

What's New in Excel 2013

See all Topics excel

Split the Costs

2018-03-02T03:06:00.000-08:00

Split the sheets (?)

Joe Chirilov presents a spreadsheet solution to a friendship breaker.

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

Split Costs

You can download a spreadsheet or watch a video here:
Split_Costs.zip

See all Topics excel

Match Format Paste

2018-02-15T03:57:00.000-08:00

Copy/Paste formatting in Word, PowerPoint or Excel

When you copy information from a Web page or another document, the formatting will also be copied.

To match the formatting of the target document, copy the text and place the cursor where you want to insert the copy.

Then, go to Edit>Paste Special, and select the Unformatted Text option.
(Click the arrow under Paste in the Clipboard group on the Home tab in 2007+)

The clipboard text will be pasted to match the target.

Another way when using Word 2002 + is to click on the "Smart icon" that appears at
the lower right corner of the pasted text. You can then choose to keep the original formatting, match the destination formatting, keep text only, or apply a new style.

An additional way to transfer just the formatting between documents is to highlight the text with the formatting you wish to copy and then hold down the Ctrl key and the Shift key and press the C key (Ctrl+Shift+C). Release the keys. Select the text you want to have formatted. Hold down the Ctrl key and the Shift key and press the V key (Ctrl+Shift+V). Only the formatting is copied, not the text.
In Excel use Edit>Paste Special and select the "Formats" option.

What's So Special About "Paste Special"? Video

Paste Special can also be used with graphics.

You can change Word's default behavior; choose whether to paste Inline or Floating.

Microsoft Word MVPS FAQ

See all Topics excel

Spreadsheet Bloat

2018-02-14T03:59:00.000-08:00

Formatting bloat

Avoid applying formatting to more than just the active area of your worksheet. Extraneous formatting will confuse Excel about the last cell in the spreadsheet.

Depending on your OS and specific configuration, you could see symptoms ranging from Excel not responding to various error messages concerning page faults, low virtual memory, and access violations. You may see a warning message; "Too many different formats."

To resolve this issue, make sure you select only the particular range of cells you want formatted when you apply specific formatting or select Format>AutoFormat.

You can use Ctrl+End to see where Excel thinks the last cell is.
For more information, check out:

How to reset the last cell in Excel

Also see:

Spreadsheet Diet

Microsoft:
Formatting Cleaner Add-in

See all Topics excel

More than Sudoku

2018-02-06T03:06:00.000-08:00

Game with Excel

"IT may be the biggest tool in the Office, but Microsoft Excel can be cool too"

Here's an article on off-prescription Excel.

Play with Excel

Here are some of the games you can play for free:

Excel fun
3D Viewer
Battleship
Blackjack
Breakout
Golf Stats
Mastermind
Maze
Minesweeper
Reversi
Rubix
Slots
Sudoku Solver
Tetris
Video Poker

ExcelTrick.com

Mashable.com

See all Topics excel

Date Arithmatic

2018-01-28T03:42:00.000-08:00

The drunken cousin

Working with dates has a few twists.

Excel believes that time began on January 1, 1900.

Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333

When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.

Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.

The correct number of days → 789 will now be displayed.

Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates
Also:

John Walenbach:
Extended Date Functions Add-In

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

MS Knowledge Base:
How To Use Dates and Times in Excel

See all Topics excel

Reset Shortcut Menu

2018-01-24T03:04:00.000-08:00

Context menu redo

For some reason (to be determined later) my shortcut menu started to accumulate a string of "Edit Formula" and "Delete Formula" entries.

They didn't cause a problem, but were distracting.

This little VBA code puts it back to the original settings until I can find a cause.

Sub ResetShortcutMenu()
Application.CommandBars("Cell").Reset
End Sub

Macros that Customize and Control Shortcut Menus

See all Topics excel

Office VBA Tricks

2018-01-23T03:02:00.000-08:00

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

Update Word Document Statistics in the Title Bar
Create Outlook Rules Programmatically
Delete Repeated Text Throughout a Word Document
Run Macros Based on the Value of One or More Excel Spreadsheet Cells
Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
Display Reminder Information When a User Opens an Office Document
Synchronize an Access Main Form to a Subform and Vice Versa
Log Worksheet Changes to an XML File
Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Ten Tips for Office VBA Developers

VBA Tips & Tricks

Getting Started with VBA in Office 2010

Download Office 2013 VBA Documentation

(VBA is VBA and is, in most cases, usable in all versions of Office)

See all Topics excel

Chiropractics for Excel

2018-01-19T03:54:00.000-08:00

Knead and pound numbers

Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.

Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

I'm sure you've been faced with at least one of theses problems:

Import the data and don't accidentally drop the leading zeros!
Formatting SSN
Inserting hyphens
Fixing up names
Lower case E-Mail Address
Format Home Phone
Inserting parenthesis & hyphens
Trim extra spaces from Address
Add City values to the new table
Make all State value upper case
Fill in Postal Code
Finalize the values

Manipulate and massage

See all Topics excel

Spreadsheet Boo-Boos

2018-01-10T03:43:00.000-08:00

Design suggestions

A few links to sites with advice on spreadsheet design and error detection.

European Spreadsheet Risks Interest Group:
Spreadsheet mistakes - news stories

Raymond R. Panko, PhD University of Hawaii

What We Know About Spreadsheet Errors

See all Topics excel

Web Data

2018-01-08T03:57:00.000-08:00

Numbers from the ether

Excel has had a feature called web query. Here's an add-in that makes it a little easier.

"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel.

The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained."

Web Data

Getting data from the Web in Excel

2013 Web Data YouTube

See all Topics excel

All the Basics

2017-12-27T03:45:00.000-08:00

All(most) all you need to know

Office.Microsoft.com has a short demo that shows you the main things anyone needs to know about Excel.

There are many thousands of users who find that this is all they ever need.

Add numbers
Subtract numbers
Multiply numbers
Divide numbers

Use simple formulas to do the math

See all Topics excel

Chart Art

2017-12-21T03:34:00.000-08:00

Apple π

Look at how different media organizations have used graphics/graphs to illustrate the news.

Lee LeFever:
The Art of Explanation

While Edward Tufte has concerns about representing data accurately in charts, he does, use pictures to demonstrate relationships.
(See Edward Tufte)

If you would like to try your hand, here are some links that will help to spice up your condiment report.

PC Magazine:
Add Images to Excel Projects

MacWorld:
Excel Chart Art

Andy Pope:
Dividing a graphic into sections

Excel 2007-13 has a great graphic look for charts, but these links still work

See all Topics excel

Loan Payment

2017-12-19T03:23:00.000-08:00

Basic tutorial

Microsoft provides a number of learning activities related to fundamental tasks.

Here's one that walks the student through a worksheet designed to calculate interest and total payment for a purchase, based on different loan terms.

"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?

Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "

Dream Car
Also:
Basic Financial Calculations

See all Topics excel

2003-07-10-13 Compatibility

2017-12-13T03:11:00.000-08:00

Exchange the future and the past

"Microsoft has added new file formats to Microsoft Office Word, Excel, and PowerPoint 2007+. To help ensure that you can exchange documents between Microsoft Office releases, Microsoft has developed a Compatibility Pack for the Office Word, Office Excel, and Office PowerPoint 2007+ File Formats"

Use earlier versions of Excel, PowerPoint, and Word to open and save files from 2007-13 Office programs

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint

See all Topics excel

Excel Links

2017-12-09T03:40:00.000-08:00

Spokes of the web

There's a lot of information out there. The problem is how to find it.
Here is a site that contains links to Excel information arranged in topics:

Excel Add-ins
Excel Help
Excel Password Recovery
Excel Templates
Excel Tips & Tricks
Excel Tutorials
Excel VBA
Free Excel Add Ins
Spreadsheet Research

Excel Links

See all Topics excel

Data Tables

2017-11-28T03:01:00.000-08:00

Up one side; down another

Data tables are a neat Excel feature that has not been emphasized.

If you are looking at, for instance, a home loan with a number of interest rates and different loan periods, a Data table can lay out the results with a minimum of fluff and formulas.

Dick Kusleika, Microsoft MVP, has a description on his excellent Daily Dose of Excel blog.
Data Table Basics

J K Pieterse:
Excel 2007, 2010, 2013 tables

Microsoft:
How to Use Microsoft Excel Data Tables to Analyze Information in a Database

How to Create and Use Two-Input Data Tables in Microsoft Excel

Overview of Data Tables - 2003 and 2007

TechRepublic.com:
Teach two-variable Excel data tables with real-life examples

See all Topics excel