How to Excel in life
April 1, 2011 at 9:32 AM | Posted in General | Leave a commentTags: life
I believe most of my readers use Excel, probably quite often – otherwise, what are you doing here? Go read something interesting!
Where was I? Oh. Some people appear to be effected by Excel more than others… Although in other cases, it’s a different kind of Excel ;->
Getting ready for Office 2010
August 20, 2010 at 9:42 AM | Posted in General | Leave a commentTags: compatibility, office 2010, tool, upgrade
If you are a part of an organization’s IT department, and getting ready to roll out Office 2010 to replace any older Office versions, you better be darn sure that all the files and macros will still be working after the upgrade, or you are going to be flooded with complaints.
To that end, Microsoft has announced the availability of the Office 2010 File compatibility Tool via the official Office blog. For corporate environments, it’s a must-use before upgrading.
Totally cool total
August 18, 2010 at 9:45 AM | Posted in General | Leave a commentTags: feature, subtotal
The other day one of my colleagues asked me if there’s a quick way to create sub-totals for a spreadsheet. That is, faster than going over them one by one and adding a new row whenever the key value changes.
Well, I went off looking for a solution, and found this post that explains the basics of the cool sub-total feature. Simple and easy.
Putting your Spreadsheet on the Web
August 17, 2010 at 9:27 AM | Posted in General | Leave a commentTags: online, PDF, publish, spreadsheet, zoho
Sometimes you need to show a spreadsheet to other people on the web. Either inside your organization, or just to the public at large. But saving an Excel file won’t do: you don’t want people to mess with your data, and a lot of them don’t even have excel or don’t want to download a file…
So, Chandoo wrote a great post about some ways to do that. Although barely touching on saving it as a PDF file, and doesn’t mention online spreadsheet applications like zoho, the ways he does mention are fully discussed, along with the limitations of each one.
Show me your formulas. And don’t touch that mouse!
August 16, 2010 at 9:35 AM | Posted in General | 1 CommentTags: formulas, keyboard, shortcut
Anybody who works with Excel for a long time tries to find tricks that can help them save time. For example moving your hand over to the mouse and clicking the ribbon a few times is time-consuming, at least if you have a shortcut available.
So, when I saw this little tip, I just had to share it. You can easily switch from viewing the values of cells to viewing the formulas (and back!) using Ctrl+Tilde (I prefer to call the top-left-most key in the keyboard Tilde because that’s what you get when you shift-press it: ‘~’. Calling it the ‘accent grave key’ is a bit much, don’t you think?)
Starting with PowerPivot’s Data Analysis
August 15, 2010 at 9:33 AM | Posted in General | Leave a commentTags: data analysis, powerpivot
A few days ago we looked at the new PowerPivot – Microsoft Excel Add-in (well, it’s a little understatement to call it just an Add-in) – and how to start using it to create a quick-and-dirty Data Warehouse.
Another excellent primer for starting with PowerPivot – this time on some Data Analysis functions – was written by Mr. Brian Egler. It’s probably enough to get you interested, and provides a link to the documentation if you need more information.
Keeping the image – smart resizing and rotation
August 8, 2010 at 9:36 AM | Posted in General | Leave a commentTags: chart, image, resize, rotate
Adding an image to Excel is easy – just click on Insert -> Picture and select it. Even resizing it easy: just grab one of the borders and pull.
Excel will even ensure that the image keeps its ratio (width to height) if you grab a corner instead of a border. But what if you want to keep the image centered? Even that is easy, if you follow this Excel tip.
Another trick is rotating the image. You can do that by grabbing the circle that appears above the image when it is selected and moving it around, as seen in the image to the right.
One more thing to know: if you want to keep the rotation to 15-degrees increments (i.e., 0, 15, 30, 45, 60, etc.), you can press the Shift key while dragging the rotation circle.
Basic Data-Warehouse using Excel
August 7, 2010 at 5:36 PM | Posted in General | 1 CommentTags: data warehouse, powerpivot, sql
As most of you probably know, a full-fledged data warehouse is usually much too complicated to create using just Excel.
Mr. Gleeson explains, however, that with Microsoft’s new PowerPivot extension (and some work) you can create some pretty impressive data-warehouse functionality directly from Excel.
VBA constants in Excel
August 6, 2010 at 9:39 AM | Posted in General | Leave a commentTags: getting-started, macro, tutorial, VBA
When you write a lot of VBA macros in Excel, you learn a lot of tricks. However, there are a lot of people starting with macros that would like some basic facts and how-tos.
In this spirit, this is a basic tutorial about Excel’s VBA pre-defined constants – what they are and how to use them. Along the way, the author also creates a simple macro by recording some actions, which is a good way to start with a macro, as I’ve already discussed.
Filter the auto-filter
August 5, 2010 at 9:19 AM | Posted in General | 1 CommentTags: auto-filter, filter, search
Auto-filter is one of my favorite features in Excel, because it lets you do advanced filtering with only a few clicks of the mouse’s button.
It’s also quite simple and does the job well, which is why I was amazed they found a way to improve it in Excel 2010…
The new Excel version has a search box above the list of values for a column. This is a fantastic new feature that makes filtering even more easy for the users.
There’s more info on how to use this (with a cool video as usual) in Contextures.
The Excel ribbon – challanged by width
August 4, 2010 at 5:19 PM | Posted in General | Leave a commentTags: customize, ribbon, ui, width
When Microsoft Office 2007 was released, the whole Office users community got up in arms about the controversial new Ribbon. Lots of people hated it, some loved it, but we all got used to it.
One thing you should know about the Ribbon – it is fluid. If you use Excel only on one computer, or in the same screen resolution, you will always see the same ribbon. But, as mentioned in this tip video, when Excel has a different width, the ribbon changes appearance: some icons appear and disappear; some are expanded or moved.
As I’ve mentioned before, in Office 2010 things got even more complicated because you can now customize or add new ribbons. A customized ribbon is just as flexible as a default one, though.
How to print the Workbook properties
August 3, 2010 at 5:36 PM | Posted in General | Leave a commentTags: print, properties, VBA
Microsoft Office documents – including Excel Worksheets, of course – have some general properties that can be edited by the document’s author and viewed by anyone accessing the document. Those include, for example, the author’s name, category, keywords, and more. You can easily see (and edit) those properties from within Excel, and also from Windows Explorer: right-click any Excel Workbook in Windows Explorer, select ‘Properties’ from the context menu, and click the ‘Summery’ tab. You can even add new properties of your own (but that’s a discussion for some other post…)
So, viewing the properties is not a problem. But what if you need to print them? Here is a helper macro that allows just that.
A formula for the workbook filename
July 10, 2010 at 9:17 AM | Posted in General | Leave a commentTags: cell, filename, parent, workbook
Let’s say you want to show the filename of the workbook, inside cell B1 (for using it in your VBA code, or in other formulas).
This is easy to accomplish. you just use the CELL function:
But this goes horribly wrong when you open two Excel files at the same time:
See how both cells show the same file name? This is outrageous, right?
It can be fixed, though, and quite easily at that. What you need is to add a reference to a cell, in order to instruct Excel not to look at the “active” workbook, but rather at the “parent” workbook, like so:
=CELL("filename", A1)
Keep that in mind.
Quotation templates from Chandoo
July 9, 2010 at 9:27 AM | Posted in General | Leave a commentTags: government, price-quote, quote, template
If you stay in the technology and information industry for a while, eventually you find yourself doing work for government entities, or such organizations that are under the supervision of governments.
These clients need to compare and approve price quotes for each and every service or product that they purchase. It’s a matter of bureaucracy and regulations.
Chandoo found himself in that spot exactly, and had to create his own quotation templates. He also decided to share these with us.
If you need to do work for clients in governmental organizations, these can save you a lot of time and frustration.
More new Office 2010 features
July 8, 2010 at 9:15 AM | Posted in General | Leave a commentTags: office 2010, pcmag
I’ve been using Office 2010 for a couple of months now (I had early access to it through a place I work for), but I would guess that for most of my readers it’s still considered brand new.
Michael J. Miller, a veteran PC-Magazine columnist, wrote about the six features he deems the most attractive for home and small business users (in his article he says 5, but I count 6, go figure).
In short, they are:
- Return of the File Menu (blessed be the guy who thought about this)
- Word’s Navigation Pane
- PowerPoint video editing
- Better Graphics Editing
- Sparklines (I’ve mentioned these a few times in the past)
- Online Apps
Colorize protected cells
July 7, 2010 at 9:54 AM | Posted in General | Leave a commentTags: conditional-formatting, lock, protect, VBA
I’ve previously mentioned the various ways of protecting parts of Excel sheets from being edited by malicious or annoying users, and also how to hide your precious formulas, and do this per-user.
There’s another side to this, though, and that’s educating users about this whole concept.
One thing that can help you a lot in this front is to mark the protected cells (or the unprotected cells) in a different color or border, etc.
Dick of “Daily dose” wrote a macro to do this automatically with conditional formatting, saving you a lot of otherwise wastes time by hunting for those elusive editable cells.
Formula for the average of the top values
July 1, 2010 at 5:47 PM | Posted in General | Leave a commentChandoo has a quick tip on how to calculate the average of only the top or bottom few values in a range.
He’s nesting LARGE and AVERAGE, as you’d expect, but raises another question as homework for the reader:
What do you do if the range itself may grow or shrink?
The comments have an in-depth discussion on that, with several suggestions.
Draw borders with the mouse
July 1, 2010 at 9:50 AM | Posted in General | Leave a commentTags: borders, mouse, ui
An Excel Tips article describes how you can draw cell borders with the mouse.
You can easily draw borders around ranges or even inside ranges, with just a simple drag-drop gesture.
I must admit that the border manipulation UI in Excel is a bit lacking sometimes, and can cause complex border work to be quite tedious.
This small feature attempts to ease the task for drawing borders, and it’s often disregarded and left unused.
Bet on 2010 FIFA World Cup games in Excel
June 30, 2010 at 5:41 PM | Posted in General | Leave a commentTags: bet, fifa, soccer
With the 2010 FIFA World Cup semi-finals and finals coming up in just a few days, Chandoo wrote a spreadsheet for managing the bets in your office.
Apparently he’s taken the idea from a Denmark office where he worked.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
