Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

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

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

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

Wednesday, August 23, 2017 – Permalink –

Location Indicator

Point to the spot

Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.

Color banding location

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

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

Friday, August 18, 2017 – Permalink –

Zeros - Before and After

Nothing's a problem

"When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data.
For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234.
This article shows you how to preserve trailing zeros when you import data into Microsoft Access."

How to Preserve Trailing Zeros When Importing Data
Also:

Word — Decimal Point or Trailing Zeros Missing When You Merge Microsoft Access Database

Excel — Using a Custom Number Format to Display Leading Zeros

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

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

Monday, March 27, 2017 – Permalink –

Stacked Columns

On on another
Instructions how to put one column on top of another in a chart.

People.stfx.ca

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

<Doug Klippert@ 3:15 AM

Comments: Post a Comment

Monday, February 27, 2017 – Permalink –

Positive Negative Colors

Vary
Change the color when going from positive to negative.

AndyPope.info

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

<Doug Klippert@ 3:38 AM

Comments: Post a Comment

Saturday, November 19, 2016 – Permalink –

Sparklines 2013

Hail Tufte
New for Excel 2010-13

See all Topics excel
Labels: Charts, Formats, Formulas, General, Reference, Tips, Tutorials

<Doug Klippert@ 8:22 AM

Comments: Post a Comment

Friday, November 04, 2016 – Permalink –

Conditional Formatting II

If, then
Jon Peltier offers a number of ways to format conditional charts.
PeltierTech.com

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

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

Thursday, September 29, 2016 – Permalink –

Color Scales

Conditional colors
Here is some information on Conditional color scales in Office 2010.

Excel-Easy

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

<Doug Klippert@ 3:29 AM

Comments: Post a Comment

Thursday, September 01, 2016 – Permalink –

Chart Null Data Gaps

Fill in the spaces

When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.

Microsoft also has this suggestion:

Gaps between the dates

"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."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.

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

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Tuesday, August 02, 2016 – Permalink –

Conditional Formatting

Much more capabilities

Pre-2007 Excel was limited to only 3 conditions. The new Office is more generous and versatile.
Here are some of the features:

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 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 style rules where you can enter a formula to determine whether a format should be applied.

2007+ Conditional Formatting

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

<Doug Klippert@ 3:24 AM

Comments: Post a Comment

Sunday, June 26, 2016 – Permalink –

Shortcut Borders

Keyboard trick

Use Ctrl+1 (on the top row of number keys) to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab.

Now you can use the following key strokes (2002+):

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.

Shortcuts for Applying Borders

Ctrl+Shift+7 will outline a cell without having to display the Format dialog.

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

<Doug Klippert@ 3:35 AM

Comments: Post a Comment

Thursday, April 07, 2016 – Permalink –

Plain Numbers

I'd Like to Make It Clear

Plain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand.

Have you ever:

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."?

Then you know how important the clear display of numerical information can be. Common problems People have trouble using numerical information for many reasons. Most commonly, authors don't know:

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.

Plain Figures is a partnership between Sally Bigwood, located in Wakefield, Yorkshire, UK, and Melissa Spore, who divides her time between Toronto and Saskatoon, Canada. Sally and Melissa are sisters and both have dual citizenship in the United States.
PlainFigures.com

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

<Doug Klippert@ 3:16 AM

Comments: Post a Comment

Tuesday, March 29, 2016 – Permalink –

Thirtieth Condition Formatting

Three is not always enough

Pre-2007 Excel gives the user the ability to specify up to three conditions under Format>Conditional Formatting.

If that is not enough, here's the code that extends the conditions to 30!

Extended Conditional Formatter

Also see:
Conditional Formatting (including 2007)

See all Topics excel
Labels: Customize, Formats, Formulas, General, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

Friday, December 11, 2015 – Permalink –

Hide Digets

Simple obfuscation

The kid said,
"Daddy, I know the secret password!
It's star, star, star, star!"
****
You can use functions to hide parts of sensitive data.

Social Security Number 555-55-5555

=CONCATENATE("***-**-", RIGHT(B2,4))

Combines the last four digits of the SSN with the "***-**-" text string

(***-**-5555)

Credit Card Number 5555-5555-5555-5555

=CONCATENATE(REPT("****-",3), RIGHT(B3,4))

Repeats the "****-" text string three times and combines the result with the last four digits of the credit card number

(****-****-****-5555)

Microsoft Office Online:
Display only the last four digits of identification numbers

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

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

Sunday, November 01, 2015 – Permalink –

Spreadsheet Design

Make it work and look good

Timothy Miller uses the nom de screen of "Jethro" (Moses' Father-in-Law).

His SpyJournal.biz site/blog gives some tips on how to present an Excel solution

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.

You'll find the complete text here:
Design Presentation Tips

Also see:
The Art of the Spreadsheet

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

<Doug Klippert@ 3:45 AM

Comments: Post a Comment

Monday, October 05, 2015 – Permalink –

Format Numbers

It's your choice

OzGrid:
Custom Formats

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

In cell A1, enter the text "You still owe the sum of".

In cell A2, enter the number "5434".

In cell A3, enter the text "for invoice # 2232 from 6/15/2001".

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

<Doug Klippert@ 3:55 AM

Comments: Post a Comment

Friday, September 25, 2015 – Permalink –

Time Without Limits

No Delimiters

Excel is most happy when you enter dates and times with the correct separators.

1/1/2004 is a good date. So is 1-1-2004.

If you just entered 112004 in a cell formatted as a date you'll get:

Wednesday, August 27, 2206

the 112,004^th day since January 1, 1900.

Chip Pearson has come up with VBA code, using the Worksheet_Change event procedure, that will allow you to enter dates without dashes or slashes.

See:
Date And Time Entry

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

<Doug Klippert@ 3:55 AM

Comments: Post a Comment

Sunday, August 30, 2015 – Permalink –

Too Many Slices

More data than the pie will hold

A pie chart displays the per cent of the whole is represented by the component elements. Four salesmen, four slices of pie.

The problem arises when there are 10 or so components that vary in size. The labels begin to overlap and the chart is difficult to read:

One suggestion that Chris Weber offers is to rearrange the order of the slices:

The article uses MS Graph in Access, but the techniques are applicable in all the other applications that can use graphs.

SmartAccess:
Easy as Pie. . .

"Chris Weber provides you with a generic method to control the data for pie charts that are actually readable."

(A downloadable example file is also provided

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

<Doug Klippert@ 3:49 AM

Comments: Post a Comment