2:52 AM
RIPON COMPUTERS
Microsoft Office Excel
syndicated content powered by Feed Burner
Subscribe Now!
...with web-based news readers. Click your choice below:
...with other readers:
FeedBurner makes it easy to receive content updates in My Yahoo!, Newsgator, Bloglines, and other news readers.
Current Feed Content
Posted: 2011-07-29 04:42:39 UTC-07:00
Game with Excel
"IT may be the biggest tool in the Office, but Microsoft Excel can be cool too"
Here's an article on off-prescription Excel.
Play with Excel
Here are some of the games you can play for free:
- Excel fun
- 3D Viewer
- Battleship
- Blackjack
- Breakout
- Golf Stats
- Mastermind
- Maze
- Minesweeper
- Reversi
- Rubix
- Slots
- Sudoku Solver
- Tetris
- Video Poker
ExcelGames.org
See all Topics excel
See all Topics excel
Posted: 2011-07-20 06:28:45 UTC-07:00
The drunken cousin
Working with dates has a few twists.
Excel believes that time began on January 1, 1900.
Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333
When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.
Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.
The correct number of days → 789 will now be displayed.
Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."
=DATEDIF(EarliestDate,LatestDate,Interval)
=DATEDIF(A2,A1,"d")
Here's THE source for date math:
Chip Pearson:
All About Dates
Also:
John Walenbach:
Extended Date Functions Add-In
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
Posted: 2011-07-15 07:05:08 UTC-07:00
Context menu redo
For some reason (to be determined later) my shortcut menu started to accumulate a string of "Edit Formula" and "Delete Formula" entries.
They didn't cause a problem, but were distracting.
This little VBA code puts it back to the original settings until I can find a cause.
Sub ResetShortcutMenu()
Application.CommandBars("Cell").Reset
End Sub
Posted: 2011-07-28 06:40:14 UTC-07:00
Video + Free code
"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."
- Update Word Document Statistics in the Title Bar
- Create Outlook Rules Programmatically
- Delete Repeated Text Throughout a Word Document
- Run Macros Based on the Value of One or More Excel Spreadsheet Cells
- Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
- Display Reminder Information When a User Opens an Office Document
- Synchronize an Access Main Form to a Subform and Vice Versa
- Log Worksheet Changes to an XML File
- Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
- Use the Office Assistant as an Alternative to Displaying and Retrieving User Input
Ten Tips for Office VBA Developers
VBA Tips & Tricks
Getting Started with VBA in Office 2010
(VBA is VBA and is, in most cases, usable in all versions of Office)
See all Topics excel
VBA Tips & Tricks
Getting Started with VBA in Office 2010
(VBA is VBA and is, in most cases, usable in all versions of Office)
See all Topics excel
Posted: 2011-07-08 03:16:00 UTC-07:00
Knead and pound numbers
Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.
Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.
This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.
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
See all Topics excel
Posted: 2011-06-29 06:51:09 UTC-07:00
Design suggestions
A few links to sites with advice on spreadsheet design and error detection.
European Spreadsheet Risks Interest Group:
Spreadsheet mistakes - news stories
John F. Raffensperger:
(oldies but . . .)
Spreadsheet Style
Microsoft Assistance:
Worksheet design strategy
Raymond R. Panko, PhD
What We Know About Spreadsheet Errors
See all Topics excel
Posted: 2011-06-25 06:42:03 UTC-07:00
Numbers from the ether
Excel has had a feature called web query. Here's an add-in that makes it a little easier.
"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel.
The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained."
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."
Posted: 2011-06-18 03:39:00 UTC-07:00
Don't hide
In Excel, if you type a stock ticker symbol, the options offered on the Smart Tag are:
- Stock quote from MSN MoneyCentral
- Company report from MSN MoneyCentral
- Recent news on MSN MoneyCentral
- Insert a refreshable stock price
- Remove the Smart Tag
- Smart Tag options (This brings up the Smart Tags tab on the AutoCorrect menu.)
How to turn on smart tags. Smart tag functionality is turned off by default. Before you can use smart tag functionality, you must turn on smart tag recognition. To do this, follow these steps, as appropriate for the version of Excel that you are running.
Microsoft Office Excel 2007
Microsoft Office Excel 2007
7. Click the Microsoft Office Button, and then click Excel Options.
8. Click Proofing.
9. Click AutoCorrect Options.
10. In the AutoCorrect dialog box, click the Smart Tags tab.
11. Click to select the Label data with smart tags check box.
12. In the Recognizers box, click to select the check boxes next to the specific smart tag recognizers that you want to turn on, and then click O13. Click OK to close the Excel Options dialog box.
Microsoft Office Excel 2003 and earlier versions of Excel
1. On the Tools menu, click AutoCorrect Options.
2. In the AutoCorrect dialog box, click the Smart Tags tab.
3. Click to select the Label data with smart tags check box.
4. In the Recognizers box, click to select the check boxes next to the specific smart tag recognizers that you want to turn on, and then click OK.
Smart tag functionality
See all Topics excel
See all Topics excel
Posted: 2011-06-12 03:37:00 UTC-07:00
All(most) all you need to know
Office.Microsoft.com has a short demo that shows you the main things anyone needs to know about Excel.
There are many thousands of users who find that this is all they ever need.
- Add numbers
- Subtract numbers
- Multiply numbers
- Divide numbers
Posted: 2011-06-04 07:32:56 UTC-07:00
Apple π
Look at how different media organizations have used graphics/graphs to illustrate the news.
Poynter Online:
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-10 has a great graphic look for charts, but these links still work
Posted: 2011-06-02 03:25:00 UTC-07:00
Basic tutorial
Microsoft provides a number of learning activities related to fundamental tasks.
Here's one that walks the student through a worksheet designed to calculate interest and total payment for a purchase, based on different loan terms.
"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?
Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "
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. "
Also:
Basic Financial Calculations
See all Topics excel
Basic Financial Calculations
See all Topics excel
Posted: 2011-05-27 03:41:00 UTC-07:00
Exchange the future and the past
"Microsoft has added new file formats to Microsoft Office Word, Excel, and PowerPoint 2007. To help ensure that you can exchange documents between Microsoft Office releases, Microsoft has developed a Compatibility Pack for the Office Word, Office Excel, and Office PowerPoint 2007 File Formats"
Posted: 2011-05-22 03:19:00 UTC-07:00
Spokes of the web
There's a lot of information out there. The problem is how to find it.
Here is a site that contains links to Excel information arranged in topics:
- Excel Add-ins
- Excel Help
- Excel Password Recovery
- Excel Templates
- Excel Tips & Tricks
- Excel Tutorials
- Excel VBA
- Free Excel Add Ins
- Spreadsheet Research
Excel Links
See all Topics excel
See all Topics excel
Posted: 2011-05-19 03:14:00 UTC-07:00
It can make life easier
Here's a collection of tips and tutorials from Microsoft about how to use MSOffice at work.
If no one's looking, you could use these hints at home too.
Ways to work smarter
See all Topics excel
Posted: 2011-05-06 07:17:00 UTC-07:00
Up one side; down another
Data tables are a neat Excel feature that has not been emphasized.
If you are looking at, for instance, a home loan with a number of interest rates and different loan periods, a Data table can lay out the results with a minimum of fluff and formulas.
Dick Kusleika, Microsoft MVP, has a description on his excellent Daily Dose of Excel blog.
Data Table Basics
J K Pieterse:
Excel 2007 tables
Microsoft:
How to Use Microsoft Excel Data Tables to Analyze Information in a Database
How to Create and Use One-Input Data Tables in Microsoft Excel
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
Posted: 2011-05-02 03:44:00 UTC-07:00
Geek fame
According to the Inside Office Blog, over 1 million people have downloaded free templates from Microsoft.
"You probably have a document you use over and over again, something you created to solve a particular problem. You may even find yourself occasionally sending the document to others in e-mail because it's so useful. Now you can share your clever solution with everyone who uses the 2007 Microsoft Office system!
People like you all over the world are allowing others to download and use their document templates on Office Online. Some of these templates have tens of thousands of downloads. They were submitted by people who either wanted to help others or show their great solutions. You can, too.
People like you all over the world are allowing others to download and use their document templates on Office Online. Some of these templates have tens of thousands of downloads. They were submitted by people who either wanted to help others or show their great solutions. You can, too.
Upload your template
See all Topics excel
Posted: 2011-04-25 03:46:00 UTC-07:00
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.
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
Nerd Bird
There are other articles in the MS library about Agents and Assistants:
Animating Office Applications with Microsoft Agent
See all Topics excel
Posted: 2011-04-05 03:31:00 UTC-07:00
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."
Office.Microsoft.com:
Calculate a running total
Posted: 2011-03-31 03:32:00 UTC-07:00
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"
Posted: 2011-03-28 03:15:01 UTC-07:00
A range of expressions
Ivan F Moala of XcelFiles.com has created a fascinating use for Excel.
The application/macro converts Bmp (24Bit & 256), Gif, Jpg, Jpeg files into Excel spreadsheets!
See the screen shots and download the free workbook:
Images to Excel
Excel 2003
This picture covers a range of A1:BL74, 4,736 cells, viewed at 15% zoom.
Posted: 2011-03-27 07:06:24 UTC-07:00
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
Posted: 2011-03-20 03:08:00 UTC-07:00
Excel reading
Bastien Mensink, from the
He has aggregated the headlines from a number of Excel Blogs.
If you don't have them as part of your RSS list, you should.
Weblog Headines
See all Topics excel
Posted: 2011-03-15 07:40:59 UTC-07:00
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
Posted: 2011-03-11 03:40:00 UTC-08:00
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.
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
End Sub
Allen Wyatt: Clearing the Undo stack
See all Topics excel
Posted: 2011-02-11 03:11:00 UTC-08:00
Control input
Mark Rowlinson provides a discussion by Kid Van Ouytsel that does an excellent job explaining data validation. He has also constructs a sample workbook that you can download and play with.
Data Validation
"Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly."
Also:
Contextures:
Excel -- Data Validation
Ozgrid:
Data Validation and Conditional Formatting
MrExcel:
Data Validation
Microsoft Office Online:
Overview and samples of data validation
Microsoft Downloads:
Sample: Data Validation
See all Topics excel
Feed Burner delivers the world's subscriptions wherever they need to go. Publish a feed for text or podcasting?
You should try FeedBurner today.
You should try FeedBurner today.
0 comments:
Post a Comment