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.
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.
Excel 2010 Keyboard Shortcuts
June 18, 2010 at 9:20 AM | Posted in General | Leave a commentTags: keyboard, shortcut, ui
The official Excel 2010 product team’s blog on MSDN is running an article in 3 parts that covers their favorite keyboard shortcuts in Excel 2010.
Their review of various keyboard shortcuts is quite overwhelming and mentioned a ton of shortcuts that are very useful and were unknown to me.
Examples are: selecting and manipulating full rows, toggling references between relative and absolute, repeating the previous action, and so on and so forth.
If you consider yourself an Excel expert, you should read the full series and see what you’re missing.
Excel Sheet Protection
June 17, 2010 at 9:55 AM | Posted in General | 1 CommentTags: password, protect, ui
It’s quite easy to protect Excel sheets against changes, and it’s also possible to protect just parts of the sheet.
Edit protection can be done to prevent a casual user from making errors and overriding formulas, or it can be locked with a password against malicious (but not too savvy) users.
It can even be locked per-user in an Active-Directory domain setup.
Just don’t forget to educate your users on how to tell that the sheet is protected and how to react to that fact.
Alerts on dashboard sheets
June 16, 2010 at 5:55 PM | Posted in General | Leave a commentTags: alert, dashboard, ui
Dashboard-style sheets are used when the user need to see a lot of summary data at once, or to monitor data that is changing.
In some cases, you may want to draw the user’s attention to some specific cell or datum, because it is especially interesting or critical. This is where a visual alert does the job perfectly.
Chandoo has a very nice writeup on how to add such alerts to dashboard sheets.
Ribbon Customizations in Excel 2010
June 15, 2010 at 9:26 AM | Posted in General | 1 CommentTags: customize, office 2010, ribbon, ui
I’ve mentioned the ability to customize Excel 2010′s ribbon before.
Here’s a nice article on how to use this new Excel 2010 feature.
On one hand, users felt that this feature was very much missing from Excel 2007, when the ribbon was introduced.
On the other hand, maybe the non-customizable ribbon of 2007 was the proper way to introduce the new concept to users, and get them acquainted with it…
Now that people are used to the ribbon, there’s no reason not to let them customize it.
What do you think?
Enter and edit data with a “Data Form”
June 11, 2010 at 9:18 AM | Posted in General | Leave a commentTags: form, record, ui
I’ve found another Excel Tip worth mentioning. This is another not well-known but very useful feature in Excel.
With the Data Form feature, you get a dialog that treats each row as a record in a database:
Excel 2010 or 2007 users will especially not know about this, because it was available in the toolbar of Excel 97/2000/2002/2003, but not in the default ribbon.
In the newer versions of Excel, you need to customize the quick access toolbar, or the ribbon (2010 only), and add the “Form” command.
Introducing: Theatre Chart
June 10, 2010 at 9:40 AM | Posted in General | Leave a commentTags: chart, html5, theatre, ui
Daniel Ferry is known for creating advanced and intriguing charts. He’s made a new type of interactive chart that I call the Theatre Chart
Obviously, I chose this name because of the shape of a theatre.
His data set is the HTML 5 readiness status of various browsers, and I’m not sure what other data this kind of chart would fit… not many data sets consist of a table of boolean values.
Scroll Lock that moves the active cell
June 7, 2010 at 5:49 PM | Posted in General | 3 CommentsTags: scroll, ui
A reader of BaconBits wanted a mixed mode of editing, where the arrow-keys move the screen like in Scroll-Lock mode, but the active cell moves around as well (the topmost-leftmost cell).
datapig has a nice answer to this, with a slight code improvement in Rick’s comment.
But I would have liked to see the active cell centered, and not top-left, so my code is a bit more complex:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (ActiveWindow.VisibleRange.Rows.Count < 3) Or (ActiveWindow.VisibleRange.Columns.Count < 3) Then
Exit Sub
End If
scroll_col = Round(ActiveWindow.VisibleRange.Columns.Count / 2)
scroll_row = Round(ActiveWindow.VisibleRange.Rows.Count / 2)
If (ActiveCell.Column > scroll_col) Then
ActiveWindow.ScrollColumn = ActiveCell.Column - scroll_col
End If
If (ActiveCell.Row > scroll_row) Then
ActiveWindow.ScrollRow = ActiveCell.Row - scroll_row
End If
End Sub
For me, this seems like a better data editing mode. What do you think?
Auto-complete and Pick-from-Drop-down-List
May 28, 2010 at 9:04 AM | Posted in General | 1 CommentTags: auto-complete, drop-down, ui
Susan Harkins of the Office blog on TechRepublic, gives a great explanation of the two often-encountered but rarely considered features for entering data in Excel.
Auto-completion is when Excel suggests a value as you type, taken from the existing values in the current column.
Pick from Drop-down List is when you press Alt-Down, and get a drop-list of all existing values in the current column
End of row/column
May 27, 2010 at 5:13 PM | Posted in General | Leave a commentTags: double-click, end, ui
Pressing the End key and then a direction key on the keyboard takes you to the last cell in a contiguous range of cells. This is very useful when you want to go to the end of a very long table, for example.
But this can be done with the mouse as well, using a double-click on the border of the cell.
This is just one of many features that you usually find out by accident rather than by an online tip, but here you go anyway 
Customize Excel’s Toolbar
May 15, 2010 at 9:39 AM | Posted in General | 1 CommentTags: customize, toolbar, ui
The veteran Excel users among you probably remember how Office 2000, XP and 2003 let you customize the toolbars any way you wanted.
In Excel 2007, Microsoft added the ribbon (which is now liked more than hated), but the UI became less-customizable because of this. Excel 2010, however, lets you customize the ribbon as well; I’ll talk about this more in future posts.
Little do people know that you CAN indeed customize the toolbar in Excel 2007. I’m talking about what is known as the Quick Access Toolbar, just above the ribbon, in the caption bar of Excel’s window.
You may reorder the buttons, remove them, or add any command button, or even a button to run a macro subroutine.
I even found this little video instruction on how to do it.
Spin-controls to move rows around
May 10, 2010 at 9:15 AM | Posted in General | Leave a commentTags: reorder, spin-control, ui
I’ve mentioned spin controls before, but in some cases they’re not used for data input, but rather for manipulation of the arrangement of data.
There’s a macro in ‘daily dose’ that shows how to use a spin control to achieve row-reordering.
Naming ranges
April 30, 2010 at 9:49 AM | Posted in General | Leave a commentTags: named-range, ui
Back to some basics…
I’ve mentioned named ranges before, and talked a bit about how to put them to good use.
Apparently, some people had trouble settings names for their cells and ranges. For us veteran users this seems like an obvious task, but maybe the UI is not friendly enough for new users?
In any case, instead of explaining where to click and how to type a name, here’s a very short and to-the-point article, with an animated screen cast for anyone who still has doubts.
Collect data from web sites into Excel
April 21, 2010 at 5:23 PM | Posted in General | Leave a commentTags: browser, drag, drop, ui
It is often interesting to see how nifty UI tricks are hidden in your software, just waiting to be unleashed.
Such is dragging data from a browser into Excel.
This works quite well with tables too. Actually much better than trying to copy and paste!
If you frequently get your data from web sites (yes, even intranet sites), then this little trick will save you great amounts of time and frustration.
The video in the article shows how dead-simple it is, really.
Selection of objects
April 17, 2010 at 6:03 PM | Posted in General | Leave a commentTags: select object, ui
If you’re using Excel since the days of yore (I started with Excel 97), you’re probably used to having a button with an arrow cursor on it that once clicked, lets you select multiple objects in the screen such as charts and images.
In Excel 2007 I don’t have that button anywhere but I found a way to add it in Pointy haired Dilbert!
Quick formulas with the CTRL key
April 17, 2010 at 9:45 AM | Posted in General | Leave a commentTags: click, ctrl, mouse, ui
Here’s a wonderful tip about Ctrl-clicking to create formulas.
I’m using this for a while, and it drastically increases the speed at which I’m writing formulas, because I no longer have to think about each cell I want to refer to.
Although I’m always trying to do things with the keyboard only, sometimes the situation just asks for a mouse click, and the click is just so much faster than typing. This is one such situation.
Advanced chart options
April 15, 2010 at 7:05 PM | Posted in General | Leave a commentTags: chart, ui
I just found a nice article about little known but very useful chart options in Excel.
I must admit that some of them I knew before, but others were very nice to discover.
All the details in WinPlanet’s article on the subject.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
