Tag Archives: macro

Just for Fun… Blackjack / 21 Probability in Excel

Using Microsoft Excel to predict 21 (Blackjack) Odds
Using Microsoft Excel to predict 21 (Blackjack) Odds

 

This spreadsheet uses macros and formulas to predict odds in the card game Blackjack (aka 21). It deals you random cards one at a time and shows you the probability of your hand value if you hit again.

[showmyads]

Calculate Blackjack Odds in Microsoft Excel
XLCalibre Blackjack in Excel

Have a look and let me know what you think (don’t forget to enable macros).

[showmyads]

By the way, if you like this you probably want to check out our Excel slide puzzle game or some of our other Just for Fun posts

HR Dashboard Interactive Employee Relations Summary

On an HR Dashboard or Balanced Scorecard it can be useful to show a summary of recent employee relations case work, things like disciplinaries, grievances, long term sickness, redundancies, maternity leave etc. But often a manager only wants to see information for a specific department. If they had all the data that would filter or sort it to their heart’s content, but on an HR dashboard this is just one of many summaries that you are delivering in a small space. So you want to give them a way of selecting the department themselves and seeing only the relevant data, without needing to look in detail at the source data.

[showmyads]

Here are two spreadsheets to help:

XLCalibre HR Dashboard Employee Relations Source
Employee Relations data source
XLCalibre HR Dashboard Employee Relations Summary
Employee Relations summary

How to get set up:

1. Save both files onto your hard drive or network

2. Open the file called XLCalibre HR Dashboard Employee Relations Summary.

3. Enable macros where prompted.

4. Use the Browse button to select the file called XLCalibre HR Dashboard Employee Relations Source.

[showmyads]

Now you’re all set up. Select cell A1 and type a department name (try something like Sales, Finance, IT, Marketing, R&D) – note that it is case sensitive. The list of employee relations cases will change.

And there you have it, an interactive summary for your HR Dashboard. Clearly this could be adapted for any number of uses in HR (reasons for absense, roles recruited, new starters or leavers etc) or other areas like sales (new orders by client, new clients by business development manager etc).

Let me know what you think, it’s great to have feedback. And if you like HR Dashboards, we have other HR Dashboard posts here!

Or, if you’d like to see some wacky macros check out the Just for Fun section!

Just for Fun… Roll the Dice

Picture this…

You’re settling down for a game of monopoly… everyone is ready around the board… you’ve exchanged banter and already started to forge alliances… and you realise you don’t have any dice! Somewhere along the last 20 years that you owned the board, the dice got separated from it, eaten by the dog maybe, who knows.

So what now?

Use Excel.

[showmyads]

Here it is, your dice-rolling spreadsheet. Just select from the dropdown how many dice you want to roll (up to 6 at a time) and press the button (make sure you have macros enabled first).

Roll the Dice
Roll the Dice

If you’re learning VBA, this is a relatively short piece of code for you to investigate. See if you can understand how it works, and think whether you would do it the same way. If not, let me know what your approach would be!

 
[showmyads]

UPDATE!:

We took some good advice and changed our VBA to avoid selecting cells. It simplifies things and with a larger file is more efficient – basically it’s the better approach. Also, if you turn your volume on that it now reads you the numbers as they come up and tells you the total at the end. And another thing, it has a Roll again? dialogue box at the end.

Just for Fun… The Amazing Macro-powered Excel Slide Puzzle

You know slide puzzles right?

XLCalibre Slide PuzzleYou have a picture set out on tiles which have been scrambled, and you have to slide them around to put the picture back together. Well now you can enjoy them in Excel!

[showmyads]

Make sure you enable macro’s and you’re ready to go. If you complete the puzzle and want to start again (or just get stuck) the spreadsheet can even shuffle the tiles for you.

If you like this check out other posts in our Just for Fun category!

Top 5 Excel Keyboard Shortcut Combos

After you start remembering your first few Excel keyboard shortcuts you notice how much time they save you, and also how you start to use some of them together very regularly. Once you’ve gotten used to using shortcuts this is the next step in speeding up your game. Here are our top 5 keyboard shortcuts that play well together:

1. Apply autofilters and freeze panes on top row CTRL+Shift+L,  Alt+WFF

Most times that you have a large array of data you will want to have autofilters and freeze panes. Therefore you may find that you use this alot. To get things just right, here’s a longer list of shortcuts to put in a realistic context…

CTRL+Home (go to cell A1)

CTRL+A (select all – ensure that all of your data is included in the autofilter)

CTRL+Shift+L (autofilters)

Down Arrow (gets you to the right row to apply freeze panes)

CTRL+Space (selects the row)

Alt+WFF (freeze panes)

CTRL+Home (takes you back to cell A1, ready to work on your data)

 

2. Unmerge all cells – CTRL+A,  Alt+HMC

Merged cells are bad. Ask anyone.

So once you know this you won’t use them ever again, so you won’t need to deal with them right? Well, unfortunately not everyone knows this fact, and so its quite likely that you’ll have to deal with other people’s merged cells. Or in my case, a work on various system-generated reports that contain merged cells. So I use a couple of quick shortcuts to remove them all.

CTRL+A (select all)

Sometimes this will only select the current region (the surrounding cells that are being used), which in many cases will be sufficient. But in my quest to eradicate merged cells from the world, I want to be extra sure. So I usually press CTRL+A twice to make sure the entire worksheet is selected.

Then I press Alt+HMC. This is a toggle, so if there are no merged cells at all in your worksheet, it will merge them all! Don’t worry though, if this is the case you’ll see this message:

If that happens just press cancel and be happy that you have a worksheet with no merged cells.

[showmyads]

 

3. Add a new worksheet and name it – Shift+F11,  Alt+HOR

Sheet4 is not the most helpful name. It could be anything. So when you need to add a new worksheet, this will save you time. Its that simple.

 

4. Close the current workbook and re-open itAlt+FC, Alt+F,1 (or in 2010 its Alt+FR1)

Why would I want to close the worksheet and reopen it? Its true, you may never need this. For me its useful for two reasons:

1. I’ve made some changes that I don’t want to keep, but I want to keep on working on the workbook. If this is the case, you’ll probably have to tell Excel that you don’t want to save changes after Alt+FC.

2. I’m working on a macro that is always on (using Worksheet_Calculate()). I will use these shortcuts to either open the workbook again and enable macros for testing (in this case I’ll start with CTRL+S to save the changes I’ve made), or to stop testing and open the workbook with macros disabled to make further changes.

5. Select column and remove/highlight duplicates CTRL+Space, Remove:Alt+AM or Highlight: Alt+HLHD

CTRL Space selects the whole column and then you can choose whether you want to remove or highlight the duplicates. If you want to do this in a row instead you should start with Shift+Space.

 

So there you have it, some quick keyboard combos. Clearly this is not an exhaustive list, and you may not use them all yourself depending on what you use Excel for.

If you like Excel tips and keyboard shortcuts, you may want to follow @XLCalibre on Twitter, Like our page on Facebook, or join our group on LinkedIn.

There is another post here with more shortcut combos.

And of course, we’ve got loads more keyboard shortcuts here…