What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Labels: Customize, Formulas, Macros, Reference, Shortcuts, Tips, VBA

Also some Windows hints

• Word
• Word VBA
• Excel
• Excel VBA
  
  and
• Windows

Labels: General, Tips, VBA

Conditional colors

Labels: 2010, Formats, General, Tips

VBA code

Labels: Reference, Tips, VBA

A hard way to do an easy job

"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string."

Labels: Formulas, Functions, Tips

Finger it out

After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.

Labels: Shortcuts, Tips

Fill in the spaces

"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Labels: Charts, Customize, Formats, Formulas, Tips

User Function

=Row()

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

=ADDRESS(row_num,column_num,abs_num)

SUBSTITUTE(text,old_text,new_text)

"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."

Labels: Functions, Tips

Quick graphic reinforcement

Labels: Charts, Fonts, Graphics, Tips

Unencrypted

"Word, Excel, and PowerPoint have been able to password protect documents for several versions by setting the 'password to open'. What we felt could be improved was the ability to enforce password strength rules, similar to what may be required when logging into your computer at work."

Labels: General, Tips

Much more capabilities

Format all cells based on their values

Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.

Format only cells that contain:

Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.

Format only top or bottom ranked values:

Use this to create top n, top n%, bottom n, bottom n% rule types.

Format only values that are above or below average:

Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.

Format only unique or duplicate values:

Use this to create rules that format unique or duplicate values.

Use a formula to determine which cells to format:

Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.

Labels: Formats, Tips

Looking good

• Boldface, italicize and underline cell content
  
• Change the size and font of your text
  
• Apply a default Excel style (i.e. dollar, percent, etc) to cells
  
• Use date and time formatting in your spreadsheet
  
• Apply shading

• Apply borders
  
• Resize rows
  
• Resize columns

• Text formatting
  
• Justify cell contents
  
• Change the direction of the text in your spreadsheet
  
• Word wrap text

• Automatically format cells based on their contents
  
• Change the margins for your printed page
  
• Add a header and footer to your printer spreadsheet

Labels: Formats, Tips, Tutorials

A multidiscipline subject

Labels: Tips

Versatile functions

1. Go to an empty cell.
   
2. Copy it
   
3. Select the "corrupted" data.
   
4. Go to Edit>Paste Special and choose Add.

Labels: Tips

Help topic locations

"Using the Help Context IDs

To make use of this file(s), locate the help topic of interest and make a note of its context ID.

Then, write a VBA subroutine that displays the topic.

You can attach the subroutine to a button, or provide some other way for the user to execute the sub.

For example, if you'd like to display the help topic that show the Date/Time Functions List,
you'll find that the context ID for that particular topic is 5199659.

The following VBA procedure displays that help topic."

Sub ShowHelp()
Application.Help "XLMAIN" & Val(Application.Version) & ".CHM" ,5199659
End Sub

Labels: Reference, Tips, Tutorials, VBA

Access to Excel

Labels: Tips

Keyboard trick

ALT+T

Apply or remove the top border.

ALT+B

Apply or remove the bottom border.

ALT+L

Apply or remove the left border.

ALT+R

Apply or remove the right border.

ALT+D

Apply or remove the downward diagonal border.

ALT+U

Apply or remove the upward diagonal border.

ALT+H

Apply or remove the horizontal interior border.

ALT+V

Apply or remove the vertical interior border.

ALT+O

outlines the cells.

ALT+I

give interior (vertical and horizontal).

ALT+N

removes all borders.

ALT+C

brings up the color palette.

Labels: Formats, Shortcuts, Tips

Excel tables

Labels: Tips

Close the spreadsheet and Excel

Labels: Customize, Tips

Protect your work

Microsoft Excel provides multiple layers of protection to allow you to control who can access and change your data:

• Worksheet protection: You can protect elements on a worksheet (for example, cells with formulas) from all user access, or you can grant individual users access to the ranges that you specify.
  
• Workbook-level protection: You can apply protection to workbook elements, and you can protect a workbook file from being viewed and edited. If a workbook is shared, you can protect it from being returned to exclusive use and prevent the change history from being deleted.

... articles address some of the more frequently asked questions about workbook and worksheet protection in Excel:

• How can I grant only a few users access to a range in my worksheet?
  
• Why are users not allowed to edit the ranges that I established permissions for?
  
• What new features are available in workbook protection?
  
• Why don't the permissions that I set on ranges in my worksheets carry over to Windows 98 computers?

Labels: General, Tips, Tutorials

Change tab names automatically

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Labels: Customize, Formats, Macros, Tips, VBA

Sample graphs

How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart.

Labels: Charts, Tips

How do I know it's real?

What is a digital certificate?

What is a digital signature?

What occurs when I use a digital signature?

What Word files can I sign?

How can I obtain a digital signature?

• Method 1: Obtain a digital certificate from a certification authority
  
• Method 2: Create your own digital certificate

Labels: Addins, General, Tips

Without Protection

Labels: Customize, General, Tips

Split between worksheets

Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False

End Sub

Labels: Macros, Tips, VBA

Transfer data

Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.

Labels: Tips, Tutorials

Office 2003 redo

1. 1. Start any Office 2003 program.
   
2. On Help menu, click Detect and Repair.
   
   
   
   
3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
   
4. Quit the application, and then click Ignore.
   
5. Click OK when you receive the following message:
   Reset of setting to default succeed.

Labels: Tips, Troubleshooting

I'd Like to Make It Clear

• squinted your eyes trying to see the numbers in a PowerPoint presentation?
  
  
• scratched your head at a charity leaflet with an indecipherable pie chart titled 'Where your donation goes' ... and set it aside?
  
  
• missed discussion at a meeting because you were busy trying to figure out the figures?
  
  
• put aside a graph or table, thinking "I'm not good with numbers."?

• what to include: when unsure what numbers are important, people frequently display them all, overpowering the reader with irrelevance.
  
  
• which format to use: the choice between text and table, table and chart, bar and pie.
  
  
• how to use the technology effectively: computer software generates graphs easily, but the results hide your point behind incomprehensible chartjunk.
  
  
• how to explain the information: selecting the right words for titles, columns and captions.

Labels: Charts, General, Tips

Three is not always enough

Labels: Formats, Tips, Tutorials, 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

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

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

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

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

New Boundaries

The total number of available columns in Excel

Old Limit: 256 (2^8)

New Limit: 16k (2^14)

The total number of available rows in Excel

Old Limit: 64k (2^16)

New Limit: 1M (2^20)

Number of unique colors allowed a single workbook

Old Limit: 56 (indexed color)

New Limit: 4.3 billion (32-bit color)

Number of conditional format conditions on a cell

Old Limit: 3 conditions

New Limit: Limited by available memory

Number of levels of sorting on a range or table

Old Limit: 3

New Limit: 64

Number of items shown in the Auto-Filter dropdown

Old Limit: 1,000

New Limit: 10,000

The total number of characters that can display in a cell

Old Limit: 1k (when the text is formatted)

New Limit: 32k or as many as will fit in the cell (regardless of formatting)

The maximum length of formulas (in characters)

Old Limit: 1k characters

New Limit: 8k characters

The number of levels of nesting that Excel allows in formulas

Old Limit: 7

New Limit: 64

Maximum number of arguments to a function

Old Limit: 30

New Limit: 255

The number of characters that can be stored and displayed in a cell formatted as Text

Old Limit: 255

New Limit: 32k

Labels: General, Tips

Spreadsheet vagaries

The 1904 date system and the 1900 date system

"The default date system in Excel 2004 for Mac is the 1904 date system. The default date system in Excel 2003 is the 1900 date system. Typically, the use of different date systems does not cause a problem.

However, if you transfer a workbook from Excel to Excel for Mac, or vice versa, and then copy a date from one workbook to the other, the date may increase or decrease by four years and one day. This issue occurs if the two workbooks use different date systems.

For example, if you copy the date 1/1/1998 from a workbook that uses the 1900 date system and then paste the date into a workbook that uses the 1904 date system, the date appears as 1/2/2002. Alternatively, if you copy the date 1/1/1998 from a workbook that uses the 1904 date system and then paste the date into a workbook that uses the 1900 date system, the date appears as 12/31/1993.

As long as you know about the date systems that your workbooks use, the different date systems should not cause a problem.

Labels: General, Tips, Troubleshooting

It's your choice

Also:

OzGrid:
Custom Formats

If you need to format numbers that will be displayed connected to text, here is one way to do it:

1. In cell A1, enter the text "You still owe the sum of".
   
2. In cell A2, enter the number "5434".
   
3. In cell A3, enter the text "for invoice # 2232 from 6/15/2001".
   
4. In cell A4, enter the formula =A1&" "&TEXT(A2,"#,##0")&" "&A3.

You still owe the sum of 5,434 for invoice # 2232 from 6/15/2001

Excel Tips
Formatting concatenated numbers and text

Or:



Also:
Excel Format Tips

Also:
A very well produced examination of the subject by Jon Peltier:
Number Formats in Microsoft Excel



See all Topics
excel

Labels: Formats, Tips, Tutorials

$ lost

If you don't see DDE, check Show all.

Also see:

Answer Box:
Numbers don't merge right in Word

and:

Cindy Meister:
Mail Merge FAQ

Here are some other Mail Merge resources:
Mail Merge Links



See all Topics
excel

Labels: Tips

No Delimiters

See:
Date And Time Entry



See all Topics
excel

Labels: Formats, Tips, VBA

Who, What, Where

Current directory

=INFO("directory")

Available bytes of memory

=INFO("memavail")

Memory in use

=INFO("memused")

Total bytes of memory

=INFO("totmem")

Number of active worksheets

=INFO("numfile")

Cell currently in the top left of the window

=INFO("origin")

Operating system

=INFO("osversion")

Recalculation mode

=INFO("recalc")

Excel version

=INFO("release")

Name of system. (PC or Mac)

=INFO("system")

More:

Excel-VBA.com:
Excel Spreadsheet Information Functions



See all Topics
excel

Labels: Functions, Tips

Highlight entries

Chip Pearson's discussion of duplicates:
Duplicate And Unique Items In Lists

Labels: Formats, Tips, Tutorials

Order tabs

• Sorting Worksheets In Alphabetical Order
  
• Sorting In Custom Order
  
• Grouping Sheets By Color

Here's the code to sort by tab color:

Sub GroupSheetsByColor()
Dim Ndx As Long
Dim Ndx2 As Long
For Ndx = 1 To Worksheets.Count - 1
For Ndx2 = Ndx To Worksheets.Count
If Worksheets(Ndx2).Tab.ColorIndex = _
Worksheets(Ndx).Tab.ColorIndex Then
Worksheets(Ndx2).Move after:=Worksheets(Ndx)
End If
Next Ndx2
Next Ndx
End Sub

Sorting Worksheets In A Workbook

(The colorindex variable chooses one of the 56 colors in Excel's basic palette.
Here are all the colors and numbers as compiled by F. David McRitchie:
Excel Colors )



See all Topics
excel

Labels: General, Tips, VBA

Scenario suggestions

"I wonder how our net profit would be affected if we could reduce our variable cost per unit by just a few cents. How much could we save if we found a lower interest rate? Wouldn't it be nice to be able to play around with some scenarios, do some "what-ifs" — without messing up your current data? It's easy with Microsoft Office Excel . You can set up "scenarios" to experiment with the data and compare the possibilities. Who knows? It could be a road map to better solutions for your business."

" To find out how to use what-if functions, follow along as this tutorial takes you step-by-step through several problems. Excel 2000 is used here to illustrate these concepts, but the process is similar in all spreadsheet programs."

Using a spreadsheet to do "what-if" analyses



See all Topics
excel

Labels: General, Tips, Tutorials

Excel Mock-up

"This article explains how to use Microsoft Excel to create prototypes of your Access databases and how to make sure your data adheres to a set of rules called normal forms. Normal forms aren't hard to understand, and they're critical to designing useful databases."

Design Access databases with normal forms and Excel

Also see:

Database normalization Webcast

An Introduction to Database Normalization

Database normalization basics

Database Normalization Basics for Developers

Also see:

Tushar-Mehta.com:
Building and using a relational database in Excel



See all Topics
excel

Labels: General, Tips

Still good after all these years

These detail the benefits and outline the steps of many Excel features. They are worth the download.

They are a great resource for learning advanced features, training, and tips on a great consistent presentation that you can use to format your workbooks.

While they were written for Microsoft Excel versions 5.0 and 7.0, they are still viable in Excel 200x.

These Experiences were developed for Microsoft Corporation by Baarns Publishing.

Here are the titles of the illustrated workbooks that are free to download:

• Subtotals Experience
  
• Group and Outline Experience
  
• Range Name Experience
  
• Functions Experience
  
• PivotTable Experience
  
• Auto Filter Experience
  
• Auto Format Experience
  
• Power Chart Experience
  
• Scenario Experience
  
• Solver Experience

Labels: General, Templates, Tips, Tutorials

No formulas

Example:

In the sheet there are two lists: List 1 is in column A (in cells A2:A10), and List 2 is in column C (in cells C2:C10).

1. In Cell B1 type "List number".
   
2. In Cells B2:B10, enter the number 1.
   
3. In Cells D2:D10, enter the number 2.
   
4. Cut Cells C2:D10 and paste them into Cell A11.
   
5. Press Ctrl+*, then press Ctrl+F3, and enter a name for the list (such as Compare).
   
6. Select cell D4 or another worksheet.
   
7. From the Data menu, select Consolidate.
   
8. Select Count as the Function.
   
9. In the Reference box, press F3 and paste the Name you defined for the list.
   
10. Click Add.
    
11. Select both Top row and Left column "Use labels in" checkboxes.
    
12. Click OK.

Labels: Formulas, Functions, Tips, Tutorials

Distribute to everyone

The pertinent file is down near the bottom of the page. Look for article 127 and download accarch127.zip.



See all Topics
excel

Labels: Tips, Tutorials

Also graphs

1. Right-click the cell that contains the comment.
   (or choose Insert Comment)
   
2. Choose Edit Comment, and clear any text from the comment.
   
3. Click on the border of the comment, to select it.
   
4. Choose Format>Comment
   (or Ctrl+1)
   
5. On the Colors and Lines tab, click the drop-down arrow for color.
   
6. Click Fill Effects
   
7. On the picture tab, click Select Picture
   
8. Locate and select the picture
   
9. Click Insert, click OK, click OK
   

Also:

Charles Maxson has some code that will place a picture in the comment box based on the contents of a cell.

" Imagine that you have a list of parts for a product and you want to assign them to cells... then your users could see them as they hover over the cells."

Excel: Code to add picture to Excel Comment

Mary Ann Richardson, on TechRepublic.com, suggests saving a graph as a JPG and using that as a picture in a comment.

Add an Excel chart to a comment

See all Topics
excel

Labels: Customize, Tips

or Footer

Sub AssignCenterHeaderToValueInA1OnActiveSheet()
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Erlandsen Data Consulting:
Insert headers and footers

Chip Pearson:
Headers and Footers

Jan's Computer literacy 101:
Excel Basics: Setup Header/Footer

University of Wisconsin at Eau Claire:
Modifying Header and Footer Information


See all Topics
excel

Labels: Formats, Macros, Tips, VBA

VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

1. Choose a blank worksheet
   
2. In cells A1 and A2, enter the values 1 and 2.
   
3. In B1 and B2, enter Jan and Feb.
   
4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B.

In cell D1 enter the formula

=VLOOKUP(C1,A1:B12,2).

Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name.

To demonstrate the problem, right-click on the heading of column B and choose Insert.

The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0.

Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number.

Press Ctrl-Z to undo the column insertion.

The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references.

You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:

=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

A more esthetically pleasing, or sophisticated, function might be:

COLUMNS(array)

This returns the number of columns in an array or reference.

The modified lookup function looks like this:

=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).


Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

From a PC Magazine article
By Neil J. Rubenking

Also:

Allan Wyatt's ExcelTips:
Using the Column Function

OzGrid:
Copy Rows
(Scroll down to about the middle of the page)



See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials, VBA

Display properties

Sub CommentThem()
 Dim cell As Range
 On Error Resume Next
 Selection.ClearComments
 On Error GoTo 0
 For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
   If cell.Formula <> "" Then
    cell.AddComment
    cell.Comment.Visible = False
    On Error Resume Next 'fails on invalid formula
    cell.Comment.Text Text:=" Address:  " & cell.Address(0, 0) & Chr(10) & _
      " Value:  " & cell.Value & Chr(10) & _
      " Format:  " & cell.NumberFormat & Chr(10) & _
      " Formula: " & cell.Formula
    On Error GoTo 0
   End If
 Next cell

End Sub

by David McRitchie

Also:
Show FORMULA of another cell in Excel

[Edited entry from 3/12/2005]



See all Topics
excel

Labels: Formulas, Tips, VBA

Only what you want

Contextures.com:
AutoFilter Tips

"Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."

Microsoft Assistance:
All about AutoFilters

Jay Walkenbach:
Displaying AutoFilter criteria

The University of North Carolina at Charlotte:
Using Custom Views with Autofilter

"If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool. Custom Views can be easily set up based on your Autofilter criteria. Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them."

Labels: Formulas, Tips

Macro control

Change the default printer

Change the default printer with a macro.

Insert headers and footers

This example macro inserts a header/footer in every worksheet in the active workbook. It also inserts the complete path to the workbook.

Print all workbooks in a folder

With these macros you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer.

Print multiple selections on one sheet

If selected multiple cell ranges is printed out on different sheets, you can use this macro example to print all the selected areas on one sheet.

Select a printer tray before printing

In Excel you don't have the opportunity to set the properties FirstPageTray or OtherPagesTray like you can in Word. It's possible to create a simple solution by using SendKeys.