Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Host your Web site with PureHost!

Sunday, April 29, 2018 – Permalink –

Date and Time Entry

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
Labels: General, Reference, Tutorials

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

Friday, April 20, 2018 – Permalink –

Week Numbers

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
Labels: Formulas, General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:25 AM

Comments: Post a Comment

Saturday, April 14, 2018 – Permalink –

Default Save

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
Labels: Customize, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:47 AM

Comments: Post a Comment

Sunday, April 08, 2018 – Permalink –

Accustom Yourself to Excel

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

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

Wednesday, April 04, 2018 – Permalink –

Camera Tool

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
Labels: Gadgets, General, Graphics, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:16 AM

Comments: Post a Comment

Wednesday, March 28, 2018 – Permalink –

OLAP Cubes

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

<Doug Klippert@ 3:50 AM

Comments: Post a Comment

Thursday, March 22, 2018 – Permalink –

VBA Named Arguments

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
Labels: VBA

<Doug Klippert@ 3:54 AM

Comments: Post a Comment

Tuesday, March 20, 2018 – Permalink –

Zero 0

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

<Doug Klippert@ 3:58 AM

Comments: Post a Comment

Friday, March 16, 2018 – Permalink –

What's New in Excel 2013

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
Labels: General, Reference

<Doug Klippert@ 3:48 AM

Comments: Post a Comment

Friday, March 02, 2018 – Permalink –

Split the Costs

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

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Thursday, February 15, 2018 – Permalink –

Match Format Paste

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

<Doug Klippert@ 3:57 AM

Comments: Post a Comment

Wednesday, February 14, 2018 – Permalink –

Spreadsheet Bloat

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
Labels: Customize, General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:59 AM

Comments: Post a Comment

Tuesday, February 06, 2018 – Permalink –

More than Sudoku

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
Labels: Gadgets, General, Reference, Templates, Tips, Tutorials

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Sunday, January 28, 2018 – Permalink –

Date Arithmatic

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
Labels: Addins, Formulas, General, Macros, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

Wednesday, January 24, 2018 – Permalink –

Reset Shortcut Menu

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
Labels: General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:04 AM

Comments: Post a Comment

Tuesday, January 23, 2018 – Permalink –

Office VBA Tricks

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

<Doug Klippert@ 3:02 AM

Comments: Post a Comment

Friday, January 19, 2018 – Permalink –

Chiropractics for Excel

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
Labels: General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:54 AM

Comments: Post a Comment

Wednesday, January 10, 2018 – Permalink –

Spreadsheet Boo-Boos

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

<Doug Klippert@ 3:43 AM

Comments: Post a Comment

Monday, January 08, 2018 – Permalink –

Web Data

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
Labels: Addins, General, Internet, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:57 AM

Comments: Post a Comment

Wednesday, December 27, 2017 – Permalink –

All the Basics

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
Labels: Formulas, Functions, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:45 AM

Comments: Post a Comment

Thursday, December 21, 2017 – Permalink –

Chart Art

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

<Doug Klippert@ 3:34 AM

Comments: Post a Comment

Tuesday, December 19, 2017 – Permalink –

Loan Payment

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
Labels: Formulas, Functions, General, Reference, Shortcuts, Templates, Tips, Tutorials

<Doug Klippert@ 3:23 AM

Comments: Post a Comment

Wednesday, December 13, 2017 – Permalink –

2003-07-10-13 Compatibility

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

<Doug Klippert@ 3:11 AM

Comments: Post a Comment

Saturday, December 09, 2017 – Permalink –

Excel Links

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

<Doug Klippert@ 3:40 AM

Comments: Post a Comment

Tuesday, November 28, 2017 – Permalink –

Data Tables

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

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

Tuesday, November 21, 2017 – Permalink –

Clippy's Revenge

Roll your own OA

The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.
If you want to play with it, see John Walkenbach's:

Create A Fake Clippy

Here is a creature you could use to replace Clippy:

Nerd Bird

There are other articles in the MS library about Agents and Assistants:

Animating Office Applications with Microsoft Agent

See all Topics excel
Labels: Customize, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:22 AM

Comments: Post a Comment

Tuesday, October 31, 2017 – Permalink –

Calculate Running Total

Using the OFFSET function

Adding up a running balance can be frustrating when new data is added or old transactions are removed.

"How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."
Cash flow using OFFSET.PDF

Office.Microsoft.com:
Calculate a running total

See all Topics excel
Labels: Formulas, Functions, General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:13 AM

Comments: Post a Comment

Thursday, October 26, 2017 – Permalink –

Insert Line Breaks With Code

Label Captions

If you've ever needed to insert line breaks in a message box prompt, you most likely built a string that incorporated a line feed or carriage return character. Unfortunately, label objects aren't as forgiving when it comes to using these characters.

If you're setting a label's Caption property with code, you'll find that the special control characters are interpreted as squares, since they're otherwise un-displayable.
To successfully insert a line break in a label caption, you need to include both a line feed character and a carriage return character, entered consecutively.

To do so, you can use the Chr() function, such as:

Me.Label1.Caption = "Line 1" & _ Chr(13) & Chr(10) & "Line 2"

However, you can also simplify your code using an built-in constant:

Me.Label1.Caption = "Line 1" & vbCrLf & "Line 2"

See all Topics excel
Labels: Formats, Functions, General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:08 AM

Comments: Post a Comment

Monday, October 23, 2017 – Permalink –

Office Art

2007+ choices

Office 2007+ uses OfficeArt to format text boxes, graphics and pictures.

It's available in Word, Excel , and PowerPoint, but it is most active in PowerPoint and Excel.

Here's a description:

Office PPT Art

See all Topics excel
Labels: General, Reference, Tips, Tutorials

<Doug Klippert@ 3:49 AM

Comments: Post a Comment

Thursday, October 12, 2017 – Permalink –

Specifications for Excel 2017-2016

More flexibility

Lots of limits have been changed with 2007. The size of a worksheet is now 1,048,576 rows by 16,384 columns. The number of undo levels has gone up to 100.

Here are some more changes:

Worksheet and workbook specifications and limits

Calculation specifications and limits

Charting specifications and limits

PivotTable and PivotChart report specifications and limits

Shared workbook specifications and limits

Excel 2007-2013 Specifications

See all Topics excel
Labels: General, Reference

<Doug Klippert@ 3:26 AM

Comments: Post a Comment

Sunday, October 08, 2017 – Permalink –

Undo Excel

Level talk

In Excel 2007+. the number of levels of the "undo stack" was increased from 16 levels to 100.

Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversible.

And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

If you think the number of undos should be changed, here's how:

Close any programs that are running.

Click Start, click Run, type regedit in the Open box, and then click OK.

In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

Microsoft Office Excel 2010

HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

Microsoft Office Excel 2007 uses Office\12.0\
Microsoft Office Excel 2003 uses Office\11.0\
Microsoft Excel 2002 uses Office\10.0\
Microsoft Excel 2000 uses Office\9.0\

On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.

On the Edit menu, click Modify.

In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.

Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Modify the number of undo levels

If you want to clear the undo stack, just run a macro such as:

Sub ClearUndo() Range("A1").Copy Range("A1") End Sub

Allen Wyatt:
Clearing the Undo stack

See all Topics excel
Labels: General, Macros, Reference, Tips, Tutorials

<Doug Klippert@ 3:14 AM

Comments: Post a Comment