It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the lunch hour.
In this example, there are three cards, each with a set of random numbers. You’ll need one of those numbered ball popper machines though, or create a number selector in Excel.
Continue reading “Create Bingo Cards in Excel”
Minimize Excel Ribbon – Save Space
If you’re working on a big worksheet, you might want to see as many rows as possible.
The Excel 2007 Ribbon uses about an inch of space at the top of the worksheet, and if you’re just entering or editing data, you probably don’t need to use the Ribbon commands.
The steps below show how to solve that problem.
Minimize or Restore the Ribbon
You can temporarily minimize the Ribbon, so it only uses a bit of space, and that will make room for a few more rows in the Excel window.
To minimize the Ribbon, double-click on the active tab.
Restore Excel Ribbon
To restore the Ribbon, so the commands are visible, double-click on the active tab.
Close Excel
Here’s one more time-saving mouse shortcut, while you’re using the Excel Ribbon:
To close Excel, double-click the Office Button, at the top left of the Ribbon.
- NOTE: If unsaved files are open, you may be prompted to save the changes.
I use this trick to close Excel, when I’m working on the left side of the Excel workbook. That way, I don’t have to travel all the way over to the Close button, at the top right!
__________________
Tiny Workouts at Your Desk
If you spend long hours working at your computer, with only an occasional jaunt to the lunch room, you might benefit from Twittercize. Ron Doyle is a Denver-based freelance writer who posts short exercises in Twitter, and they’re designed for you to do in a minute or so at your desk.
During the workday, he posts about one tip per hour. For example,
Dilberts: Sit upright, cross arms at chest, contract stomach muscles. Bow forward like you’re banging your head on your desk! 30 times slow!
or
Hulk Hogans: Elbows up, hands at your heart, pull back like Hulk ripping a yellow tank top! Squeeze those shoulder blades 30 times!
Twittercize is free and much easier than getting out to the gym for a workout, although there’s a disclaimer that you should consult your physician before starting any exercise program.
You can follow Ron in Twitter at twitter.com/twittercize.
__________________
Create a List of What Is On Your Computer
An important step in setting up my new laptop is figuring out what’s on my old laptop. To get a complete list of installed programs, plus details on hardware, installed security updates, and other details, you can use the free program, Belarc Advisor.
Download and install the program, then run it to audit your computer. The results show up in your web browser, although all the details are stored on your computer, not uploaded to the web.
The web page shows your overall audit score, virus protection information, and the status of all Microsoft Security Updates.
_______________________
Clear Dead Links From Your Bookmarks
I’m setting up my new laptop, and plan to copy my bookmarks from the old laptop. Before I copy them, I’ll clear out the dead links, to tidy things up a bit. There’s no point in copying useless data to the new machine.
If you’d like to do this on your machine, you can use the free utility AM-Deadlink. It finds dead links and duplicate links in your list of browser bookmarks.
It’s easy to install and use, and takes only a couple of minutes to check my bookmarks (about 1700). Then I sort the list by error number, and delete any 404 pages.
You can also use AM-Deadlink to create backup files for the bookmarks.
___________________________
Excel Jawbreak Game
It’s finally Friday, so here’s another Excel game to help you relax this weekend.
This is a Jawbreak game, adapted for Excel by Andy Pope, who is a very creative guy.
Built on Excel UserForm
Like Doug Glancy’s Concentration game, this game is based on a UserForm, with a button on the worksheet to start the game.
How to Play Jawbreak
The object is to clear all the balls from the screen, by selecting and deleting matching balls.
Remaining balls drop down to fill in the gaps, and when you clean an entire column, the other columns will shift left.
Easy to Play
I enjoyed the game, which is simple to play, but takes a bit of thinking to plan your moves. It keeps a list of high scores, so you can try to beat your previous best.
You can download the Excel Jawbreak game from Andy’s website. While you’re there, take a few minutes to look at some of the other brilliant things that Andy has done.
_________________
Create a Font From Your Handwriting
At YourFonts.com you can create a font based on a sample of your handwriting, then use it in Excel, Word, or other programs.
My handwriting is terrible, much to my mother’s dismay, so I thought this might give documents a personal touch, with a bit more legibility. I could use my best writing to create a sample of each letter, then use the personalized font in my documents.
How It Works
The process is quite simple. You download a blank template, and fill in the blanks with a sample of your handwriting.
You can also include your signature, which will be mapped to the caret (^) character.
Then, scan and upload the template.
A few minutes later, you can preview, then download and install your font.
Use Your Font
Once the font is installed, it should appear in the font dropdown list in Excel, Word, and other programs.
Here’s my font in Excel. Trust me, this looks better than my regular handwriting. I’m sure yours is much nicer!
What I’d Do Next Time
I’ll probably create another font from a sample of my printing, instead of cursive letters. The letters are a bit far apart, and the tails don’t connect to the next letter.
If you’re going to use this font in Excel, you might want to use the caret as a caret, instead of storing your signature in that character. Otherwise, you’ll have to change to a different font to use the exponentiation operator.
______________________
Sports Analysis in Excel
One of my clients coached his women’s hockey team to a provincial championship last weekend (and this is the medal they were awarded). Congratulations to him and the team!
I don’t think he used Excel in planning his coaching strategy, but I’m sure Excel has other uses in sports.
Assign Baseball Players
For example, one of the Excel sample files on my website lets you assign baseball players for each inning. After you assign a player in an inning, that player’s name is removed from the drop down list for the inning.
You can’t accidentally assign the same player twice. You can download the zipped file here: DataValPlayerInnings.zip 3kb
Schedule Tee Off Times
There are also a couple of sample Excel file in which you can assign tee off times for a golf event. One is worksheet based, and uses an Advanced Filter to schedule selected players in specific time slot. Download it here: GolfTeeOff.zip 11 kb
The other version has a UserForm in which to enter the tee off data: GolfTeeOffForm.zip 16 kb
Analyze Walking Targets
In another sample file, you can record the number of steps you’ve walked each day, and formulas will calculate if you’ve reached the thresholds that you’ve set.
It keeps track of your streaks, such as the greatest number of consecutive days that you’ve achieved each target.
You could adapt this to other sports or personal goals.
Download the file here: WalkTrack.zip 8 kb
Hockey Analysis
Sadly, I don’t have any hockey sample files, but I found a Hockey Analytics site that has Excel and pdf files you can download.
For example, there are several Excel files with calculations for Individual Player Contributions, and a few with Personal Goals Against Averages.
If you like statistics and hockey, you’re sure to find something of interest. But remember, as the Hockey Analytics site points out:
Baseball is a game of a limited number of states…It can be modeled accurately in discrete steps…This ain’t the case with hockey. Hockey is fluid and can only be modeled approximately…Hockey statistics are terribly incomplete.
_____________________
Open Excel Files Quickly-Desktop Shortcuts
While working at a client’s office, I sometimes create an Excel workbook that a user will enter data in every day.
To make it easy to open the workbook, I add a shortcut to the user’s desktop (with their permission, of course).
This makes it easy for the user to open the file, without trying to remember where it’s saved.
Continue reading “Open Excel Files Quickly-Desktop Shortcuts”
Create Dynamic Ranges – Excel Macro
Roger Govier has taken the pain out of creating dynamic ranges, by writing an Excel macro to automatically create the ranges for you.
What Macro Does
When you run the macro in Excel, it creates a series of names for each of the column headings on the sheet.
In addition to the Names for the column ranges, 3 extra names are defined:
- lrow for the last row number used on the sheet
- lcol for the last column number used on the sheet
- myData for the complete range of Data including the Header row.
INDEX Function
Roger uses the INDEX function in the names, instead of OFFSET, which is volatile, and could slow down your workbook.
Get the Dynamic Range Workbook
To see the code, and read a detailed description of Roger’s technique, you can visit the Create Dynamic Ranges With a Macro page on my Contextures site.
You can download the zipped sample file there, to see how Roger’s technique works.
___________________