Excel – about rows and columns
Moving and counting rows or columnsNavigation Excel has some clever keyboard shortcuts to let you move to the extremes of your worksheet. ‘Extremes’ doesn’t mean the absolute end of the worksheet, rather the end of the current block of filled or unfilled cells. Hold down the Ctrl key and press the right-arrow key – the […]
Office 2010 – about the 64-bit version
Office 2010 will have a 64-bit version but don’t get too excited. Office 2010 comes with 64-bit software as well as the usual 32-bit software. First a little background for those of us who think that 64-bit is a new type of chocolate cookie “64 bits in every bite!” Microsoft Office, like the computers that […]
Status Bar stats in Excel
Excel 2007 and 2010 have a quick way to see basic stats on a list of numbers. If you only need some basic details about a list of numbers, there’s no need to use Excel formulas or the Descriptive Statistics feature. In Excel 2003 you can select two or more cells to see a total […]
Excel Security patches
March 2010 is the month for many Excel security patches This month Microsoft has released patches for many security breaches in Excel – from Excel 2002 onwards plus Office for Mac. More embarrassing, but probably inevitable, the ‘new’ OpenXML document formats have been breached for the first time. A hacked .xlsx file could gain access […]
Quick and easy statistics in Excel
We bring together earlier articles to create a quick and simple Excel data analysis. by David Goodmanson In the final article of this series we will bring together what we have learnt in earlier articles to create a data analysis. We started with Average / Mean options in Excel then Median and Mode functions in […]
Sparklines for Excel now
An add-in puts the coming Excel 2010 feature into Excel 2007, 2003 and XP. As we’ve already noted, the Excel 2010 feature Sparklines isn’t a new idea. The idea of small graphs next to the numbers it was originally named by Edward Tufte. If you like the idea of Sparklines you don’t necessarily need to […]
Variability in Excel
A primer on measuring data variance in Excel. by David Goodmanson Let’s continue our look at the basic statistics functions in Excel. In our first article we looked at statistical functions that measured central tendency or averages. Most people understand Average or Mean though Median is often misused. Now lets look at a more difficult […]
Importing Excel Data
Looks at importing Excel data to Access databases. Q: Graham Alison has two Excel workbooks, and when he imports them into an Access database, he just gets the data, not the formats that make the spreadsheets work. A: Data is all you will get when importing from Excel. If you need special formatting or calculations, […]
The Office Envelope Object and Office 2000
Using the Office Envelope Object in Office 2000 – what works and what doesn’t. Some readers have asked whether the Office Envelope Object (featured in Access Archon #122) can be used in Office 2000. Well, sort of. While writing the article, I tested the two procedures in Office 2000 and found that the Word procedure […]
Creating an Excel timesheet
How to create an Excel timesheet, and export data from an Access timesheet. Q: Maurice writes that he needs to export Access timesheet data to an Excel worksheet for distribution, since some users don’t have Access, so he can’t just create an Access report. Is there a way to do this? A: I made a […]
Function name changes in Excel 2010
Excel 2010 changes function names like STDEV and VAR partly for Microsoft’s own benefit. Excel 2010 has revamped all the Standard Deviation STDEV and VAR functions with the old version ‘deprecated’ or demoted. There is now STDEV.S and STDEV.P for the sample and population versions of the function. STDEV and STDEVP are still available for […]
Median and Mode functions in Excel
More useful stats functions in Excel by David Goodmanson Continuing our series on Excel’s statistical functions … we started with Average/Mean and now move onto the misunderstood cousin Median plus also Mode. Median and Mode If we are looking for work and are told that the average salary is $45,000, then we have an idea […]
Making a complex Excel cell easy to use
Create the ultimate lookup or decision formula; a User Defined Function using Visual Basic for Applications. by David Goodmanson Following our recent theme of reference, decision and lookup functions, in this article we will go one step further and create the ultimate lookup or decision formula; a User Defined Function (UDF), using Visual Basic for […]
More nested IF options
Excel alternatives to nested IF’s from our readers. After David Goodmanson’s features on IF and nested IF’s we’ve received many messages from readers suggesting another alternative – CHOOSE() and here even more possiblities. Tony D writes: “ Small snippet for your “IF” library next time. I’m sure there will be hundreds of better ideas, but you […]
Office 2007 Service Pack 2 – some time on
A look at the progress of Office 2007 SP2 – bugs fixed or not. It has been over a month since Office 2007 Service Pack 2 was released. Here’s a look Outlook 2007 won’t start We mentioned reports of Outlook 2007 not starting after SP2 was installed. Those initial reports appear to be a problem […]
Excel – Nested IFs and alternatives
A look at nested IF statements and alternatives that are easier to make and understand later. by David Goodmanson In my last article I looked at a simple Excel IF statement plus their useful cousins CountIf and SumIF. I also touched on the nested IF statement, where there are many IF functions in one cell. […]
