Three is not always enough

Labels: Formats, Tips, Tutorials, VBA

Install more

A small amount of sample clip art images was included The 2007 Office systems and Office 2003 and is part of the "local collection" that is searched when you do not have Internet access to the Microsoft Office Online Clip Art and Media Web site. Office 2003 no longer included a media content CD with additional clip art. However, the Microsoft Office XP Media Content CD can still be installed locally or on a network share.

The Office XP Media Content CD contains approximately 35,000 clips that are a subset of the clips that are available on the Microsoft Office Online Clip Art and Media Web site. The Office XP Media Content CD was included with Microsoft Office XP Professional, Microsoft Office XP Standard, and Microsoft Publisher 2002 Deluxe Edition.

To install the contents of the Office XP Media Content CD on a computer, follow these steps:

1. Exit all programs that are running
   
   
2. Insert the Office XP Media Content CD into the CD drive or into the DVD drive
   (Hold down the SHIFT key to prevent the program from automatically starting. If Microsoft Windows Installer automatically starts, click Cancel)
   
   
3. Click Start, click Run, type the following command, and then click OK:
   

   msiexec.exe /i CD_drive:\CAG.MSI ADDLOCAL=ALL /qb

(CD_drive is the letter of the drive that contains the Office XP Media Content CD)

Labels: Graphics

Explicit protection

1. Open a module (start the VBA Editor)
   
   
2. Choose Tools>Options from the menu bar
   
   
3. On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel
   
   
4. Finally, click OK

Labels: VBA

Gregorian to/from Julian

The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.

"Many applications (especially mainframe systems) store dates in the Julian format, which is a 5-digit number, consisting of a 2-digit year and a 3-digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year. Excel does not support Julian dates directly, but you can use them with only a few fairly simple formulas.

Converting A Standard Date To A Julian Date

The formula below will convert a standard Excel date in A1 to a Julian Date.

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")

This formula takes the 2 right-most characters of the YEAR of the date in A1, and then appends the number of days between the date in A1 and the 0th day of that year. The TEXT function formats the day-of-year number as three digits, with leading zeros if necessary.

Converting A Julian Date To A Standard Date

The formula below will convert a Julian date to a standard Excel date.

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros."

Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date.

Labels: Link, Reference

I bid 3 Red

What if you would like to know the color name or to count or to sum cells by a fill color? There is no built-in function in Excel.

In this case you can make a User Defined Function (UDF).

Here is the sample UDF that you can use to analyze, count and sum the cells depending on their filled color.

These UDF function can be used in the same way as built-in functions that you can use in the worksheet.

• AnalyzeColor Returns the color name, the color index or color index in RGB.
  Syntax: AnalyzeColor(color range, optional; "text" or "index" or "rgb". When it is omitted "text" is used.)
  
  
  
• CountColor Counts the number of cells depending on their filled color.
  Syntax: CountColor(color range, target range)
  
  
  
• SumColor Adds all the numbers in a range of cells depending on their filled color.
  Syntax: SumColor(color range, target range)

Labels: Functions, VBA

Define Print & View Areas

"Using the Report Manager add-in program, you can combine worksheets , views (a set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.), and scenarios (a named set of input values that you can substitute in a worksheet model.) into printable reports. For example, if you have a Best Case and a Worst case scenario, a Summary view, and a Details view, you can create a report that presents the Best Case scenario in the Details view and another report that presents the Best Case Scenario in the Summary view. Reports that you create are automatically saved with your workbook so that you can print them at any time."

Create a report for printing

1. On the View menu, click Report Manager.
   
2. Click Add.
   
3. In the Report Name box, type a name for the report.
   
4. In the Sheet box, click the sheet you want to use for the first section of the report.
   
5. Do one of the following:
   
   • To use a view for the first section of the report, select the View check box, and then click the view in the View box.
     
   • To use a scenario for the first section of the report, select the Scenario check box, and then click the scenario in the Scenario box.
     
6. Click Add to enter the view or scenario as a section in the Sections in this Report box.
   
7. Repeat step 5 and 6 until you've created all of the sections you want in the report.
   
8. To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
   
9. To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
   

Edit a report for printing

1. On the View menu, click Report Manager.
   
2. In the Reports box, click the report you want to edit, and then click Edit.
   
3. Do one or more of the following:
   
   • To add a new section, click the sheet, view, and scenario you want under Section to Add, and then click Add.
     
   • To delete a section, click the section in the Sections in this report box, and then click Delete.
     
   • To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
     
   • To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
     
   Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

Print a report

1. On the View menu, click Report Manager.
   
2. In the Reports box, click the report you want to print.
   
3. Click Print.
   
4. In the Copies box, type the number of copies you want to print.
   

Labels: Addins, Tips

How to use names in Excel

• How To Define Range Names
  
  
• How To Use Range Names
  
  
• Absolute And Relative Addressing
  
  
• The Context Of Names
  
  
• Special Names
  
  
• A Step Further: A Formula In A Defined Name
  
  
• Dynamic Names
  
  
• Passing Arguments To A Defined Name Formula
  
  
• Bugs in Excel's Name Object

Labels: Formulas, Functions, Tips

Things should to function

"Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions.
The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel's limited library of formulas and functions. A UDF is simply a function that you create yourself with VBA.

The following is a sample that is a good candidate for a UDF:

Function CtoF(Centigrade)
CtoF = Centigrade * 9 / 5 + 32
End Function

In the Worksheet you would enter something like:
=CtoF(A1)

Labels: Formulas, Functions, Tips

Count to 52

Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), _
Count:=(52 - Worksheets.Count)
iWeek = 1
For Each sht In Worksheets
sht.Name = "Week " & Format(iWeek, "00")
iWeek = iWeek + 1
Next sht
Application.ScreenUpdating = True
End Sub

Labels: Formats, Macros, Tips

Sometimes we must go next door

Getting data from other sheets can be very easy: or it can be very hard, it all depends on what you need to do. This article looks at some of the ways you can get data from other sheets.

• The basics
  
• Using the Indirect function
  
• Back to the old school, XLM! (previous and next sheet)
  
• VBA user defined functions (previous and next sheet)
  
• Links

Labels: Reference, Tips

That was no font, that was my typeface

Words into Type

Stop Stealing Sheep & Find Out How Type Works

The Elements of Typographic Style

by Robert Bringhurst ISBN 0881791326

A font can be defined as a collection of characters with the same style and size. A typeface is the design of the characters regardless of size or style. The terms are used interchangeably today.

Labels: Fonts

Search with Excel, Word, PowerPoint

"Google Search 6.0/7.0 places a new sub-menu item under the Help menu of whatever program you call it from. When that item is selected, up pops a user-friendly interface. This allows a largely intuitive completion and execution of a Google Search.

On clicking the Search button a lot goes on behind the scenes.

• Your default (i.e. your usual) web browser is loaded and, without further instructions,
  
• It's off to the Google Advanced Search Page.
  
• It then fills in an Advanced Query to your specifications
  
• Executes that query.
  
• Once results are found (or not found) you are shown those results just as if you had carried out all of the steps of the process."
  

Labels: Addins

A very useful Excel feature

Labels: Formulas

No Excel needed

"Keep your records, lists and spreadsheets in one place online. Easily slice, dice and share with others.

With a free account, you can...

• Access your spreadsheets from anywhere
  
• Share your spreadsheets easily - just email the URL
  
• Add spreadsheets to your weblog
  
• FREE storage

Labels: Internet, Tips

Date an Octothorpe

"Bell Labs engineer, Don Macpherson, went to instruct their first client, the Mayo Clinic, in the use of the new (touch tone phone system). He felt the need for a fresh and unambiguous name for the # symbol. His reasoning that led to the new word was roughly that it had eight points, so ought to start with octo-. He was apparently at that time active in a group that was trying to get the Olympic medals of the athlete Jim Thorpe returned from Sweden, so he decided to add thorpe to the end."

Labels: Formats, Shortcuts, Tips

Outlook, Excel, and VBA

• Mail Workbook
• Mail one Sheet
• Mail more than one Sheet
• Mail the Selection or range
• Mail Every Worksheet with Address in cell A1
• Mail sheet or sheets to one or more people
• Mail range or sheet in the body of the mail (Send personalized email)
• Mail a message to each person in a range with Outlook
• Mail a message to each person in a range with CDO (no security warnings)
• Sending a different file to each person in a range with Outlook
• Zip the ActiveWorkbook and mail it with Outlook
• Security (Prevent displaying the dialog to Send or not Send)

Also Download Addins for Excel e-mail information

Also see:

John Walkenbach:
Sending Personalized Email from Excel




See all Topics
excel

Labels: Addins, Tips, Tutorials, VBA

Folder size list

1. Copy the code and open Excel.
   
2. Press Alt+F11 and, if necessary, on the Visual Basic Editor menu, Insert>Module
   
3. Paste the code.
   
4. You could use Alt+Tab to bring the worksheet forward.
   
5. Go to Tools>Macros and run the Macro named "GetFolderListing".

Labels: General, Tips, VBA

Lost functionality

MORE INFORMATION
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.

"A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.

Labels: Tips, Troubleshooting

English Excel

"This web page contains material for the computing and data analysis elements of the first-year PHYS1B40 Practical Skills course.

The main elements of the course are:

Analysis of data and treatment of uncertainties of measurement (Data Analysis) - discussed in lectures and illustrated by exercises using Excel

Use of spreadsheets (Excel) for

Analysis and presentation of experimental data

Mathematical modeling, simulation, analysis and problem solving

Introduction to computer programming by means of Visual Basic for Applications (VBA)

Labels: Tutorials

Do You Question the Web?

"Generally, though, people tend to overlook the option of using the Web as a data source for Excel, be that source the Internet, an intranet, an extranet, or a Web Service. But they shouldn't. Web queries are an easy, yet remarkably flexible and predictable way of bringing data into Microsoft Excel from anywhere on the Web. You can point a Web query at any HTML document that resides on any Web server - or even on a file server, for that matter - and pull part or all of the contents back into your spreadsheet...When you start using Excel's Web queries, you will realize they are almost as limitless as the Web is.

Labels: Queries, Tutorials

So cool!

There are three states that a worksheet can be in; Minimized, Maximized, and Normal.

This macro will gradually resize a worksheet from small to Maximized. The worksheet appears to be growing:

Sub SheetGrow()
Dim x As Integer
With ActiveWindow
.WindowState = xlNormal
.Top = 1
.Left = 1
.Height = 50
.Width = 50

For x = 50 To Application.UsableHeight
.Height = x
Next x

For x = 50 To Application.UsableWidth
.Width = x
Next x

.WindowState = xlMaximized
End With
End Sub

Labels: Customize, Tips, VBA

Simple obfuscation

Labels: Functions, Tips, Tutorials

Free business advice

SCORE's 10,500 volunteers have more than 600 business skills. Volunteers share their wisdom and lessons learned in business. Our volunteers are working/retired business owners, executives and corporate leaders.

• SCORE offers Ask SCORE email advice online.
  
• Face-to-face small business counseling at 389 chapter offices.
  
• Low-cost workshops at 389 chapter offices nationwide.
  
• "How to" articles and business templates

A Business Plan for a Start-up Business

Microsoft Word

A Business Plan for an Established Business

Microsoft Word

Bank Loan Request for Small Business

Microsoft Word

Break-Even Analysis

Excel

Competitive Analysis

Microsoft Word

Financial History & Ratios

Excel

Loan Amortization Schedule

Excel

Opening Day Balance Sheet

Excel

Personal Financial Statement
Excel

Projected Balance Sheet

Excel

Start-up Expenses

Excel

4-Year Profit Projection

Excel

12-Month Cash Flow Statement

Excel

12-Month Profit and Loss Projection

Excel

12-Month Sales Forecast

Excel

Labels: Templates

Show the star

1. Create an AutoShape on the Worksheet
   
2. With the shape selected, type an equal sign in the formula bar.
   
3. Enter the address of the linking cell (or click the cell)
   
4. Hit Enter

Labels: Customize, Link, Tips

Accessibility Shortcuts

Several resources are available to help increase speed and effectiveness for keyboard users. Here are keyboard shortcuts for leading Microsoft products that help save time and effort and provide an essential tool for some people with mobility impairments.

• Internet Explorer 7/6/5/4
  
• Office (2007/2003/XP/2002/2000/97)
  
• PhotoDraw
  
• PowerPoint
  
• Producer
  
• Publisher
  
• Windows (Vista/2003/XP/2000/98/ME/NT)
  
• Windows Media Player
  
• Windows Movie Maker
  
• Windows XP Media Center Edition 2005

Labels: Shortcuts

Financial enlightenment

Time Value (xls)

Introduction to time value concepts, such as present value, internal rate of return, etc.

Lease or Buy a Car (xls)

Basic spreadsheet for deciding to buy or lease a car.

Top Five NPV & IRR (xls

Explains Internal Rate of Return, compares projects, etc.

Statements (xls)

Generate a set of financial statements using two input sheets - operational data and financial data.

Business Financial Analysis Template

for start-up businesses from Small Business Technology Center

Income Statement

What If Analysis

Assessment Templates

Set of templates for assessing an organization based on the Malcolm Baldrige Quality Model.

Labels: Templates

Remember it the way you want to

"Most communities in the fifties had small town print shops that doubled as printers of local news and advertising papers. These printers could not afford graphic artists so they used stock clipart supplied by large companies who distributed common graphics for use in advertising sections of the papers. They were provided for the printer in lots of categories to meet any advertiser's needs."

Labels: Graphics

CrossNumber puzzles

"Sudoku , sometimes spelled Su Doku, is a logic based placement puzzle, also known as Number Place in the United States. The aim of the canonical puzzle is to enter a numerical digit from 1 through 9 in each cell of a 9x9 grid made up of 3x3 subgrids (called "regions"), starting with various digits given in some cells (the "givens"). Each row, column, and region must contain only one instance of each numeral.
Completing the puzzle requires patience and logical ability. Its grid layout is reminiscent of other newspaper puzzles like crosswords and chess problems.
Although first published in 1979, Sudoku initially became popular in Japan in 1986 and attained international popularity in 2005."

1. Enter puzzle values in Starting position grid
   
2. Set Game state to 1
   
3. Press F9 to calculate
   
4. The Possible numbers grid will show the first step in the solution
   
5. The Final position will show the current result
   
6. Continue to repeat calculation by pressing F9 until the puzzle is solved or the solver stops responding

Labels: General

Make it work and look good

Design and layout

One of the easiest ways to set up spreadsheets that calculate or generate results that need to be reported is to separate the function from the form. Just like a shiny exterior on a car hides the internal engine and wiring. I always create my reports and front end menus to look good and generate results and calculations in more functional sheets.

Hiding unnecessary sections

If you must have calculations and working sections visible, then hide the unnecessary bits. Hiding a row or column is only one way of doing this. Using the group function you can rollup whole rows of information, e.g. components that add to a subtotal or constants and variables such as exchange rates, interest rates, and other indexes.

Use of colour and graphics

I like to use the company logo or other graphic as a design element in my spreadsheet. Sometimes I do this by using the corporate colours, other times by using the graphic itself. If I have a spreadsheet with a lot of macro buttons, I may use command objects and use the logo as a picture on the button.

Removing excel components

There are a number of excel components that you can turn off. Menu screens and reports screens may not need horizontal or vertical scroll bars, sheet tabs or row and column headings. Using macro buttons to return to a menu can overcome the need for sheet tabs. Not displaying gridlines will give a clean uncluttered look to a layout, and then using borders as necessary can create emphasis in the right areas.

Labels: Formats, Tips, Tutorials

Statistical collection

• Charting Data
  
• Data Presentation
  
• Cumulative Frequencies
  
• Fishbone Diagram
  
• Flowcharts
  
• Frequency Distributions
  
• Gantt; PERT Charts
  
• Graphing With Excel
  
• Analyzing and Plotting Data with Excel
  
• Organizational Charts/Mapping
  
• Social Network Analysis

Labels: Charts, General, Lists

Sprinkle carefully

• Display clip-related toolbars
  
• Customizing your clip art
  
• Cropping
  
• Sizing
  
• Adding text wrapping
  
• Blurring
  
• Rotating and flipping
  
• Adding a drop shadow

Labels: Graphics