Tag Archive for 'Microsoft Excel'

Excel Tip: Conditional Formatting

Conditional formatting is a feature within Microsoft Excel which is used for highlighting cells with a certain color, depending on the cell’s value. It can be a great tool for your Excel sheets, as it has various purposes and outcomes, whether you are after the top 5 values or a simple way to values that need your urgent attention.

 

To get to conditional formatting, go to the Home tab, then to the Styles command group:

conditional formating

 

There are plenty of in-built options for users who don’t want to modify anything or want to do things on the go. However, Excel gives users the chance to customize their own rules for conditional formatting for that added personal touch.

 

Conditional Formatting

 

To customize your own rules, follow the same steps above, select the New Rule command to bring the New Rule Dialogue Box.

 

Want to learn more? Check out our Courses and our other Hints and Tips

 

Microsoft Excel: Your Christmas List

Christmas is nearly upon us and we know how stressful it can be trying to budget and keep on track of finances. So here’s a helpful tip showing how you can use Microsoft Excel to plan ahead.

 

Using Excel create you Christmas List. The one below is from one of our staff:

 

Christmas List 1

 

 

 

 

 

 

 

 

 

Using “=SUM(D5:D11)” we have calculated the total cost that has been spent on the presents.  With a small budget of £100 we can also calculate what’s left to spend.

 

 

 

 

 

 

 

 

 

 

Now using the Filter function in the Data tab we can see what has been bought for each person. This works great if you have many presents like Santa.

 

Now when you click the down arrow next to “Who for?” we get the options below.

Christmas List 4

 

Below we can see what happens when “Mum” and “Blanks” are selected. Excel counted our calculations as a default but this can be changed. Right next to the Filter button used earlier is a smaller “Advanced” button. In there you can define your range. (So Mum has 3 presents)

 

Christmas List 7Christmas List 8

 

We hope you found this helpful. Look out for more tips on our blog Microsoft Excel Tips page and on twitter www.twitter.com/infero_training

Microsoft Excel VBA Tips: How to make a message box

Using Visual Basic of Applications can make your Excel workbooks a lot more user friendly and functional. In this blog post we will be looking how to create a message box in VBA for Excel 2007, 2010, and 2013.

 

The first message box we will be creating will be a small message box which shows the text “Hello World”. Before we can do this, we need to enable the developer tab on the ribbon. To do this you need to Click on File > Options > Customise Ribbon > and then check the developer tab like in the image below.

 How to make a message box 1

 

So on the developer tab; on the left hand side you can see a coding section. This is where you can easily access the Visual Basic Software, Macros and other options.

 

How to make a message box 2

 

Clicking the Visual Basic button will open up the Visual Basic Software. When making a message box, you are unable to record a macro to make this for you. You will need to type code to do it.

 

Here is what the Visual Basic Software looks like.

 

How to make a message box 3

 

To write some code we need to create a module to hold the code. To do this go to the top bar, click on Insert > Module

 

How to make a message box 4

 

Once you have done that you need then to create a Procedure to hold the Message Box.

Click Insert > Procedure > Type a name > Leave it set to Sub and Public > Click OK.

Note: The name cannot have a space in it.

How to make a message box 5


In the Coding window you can then type the following to create a message box

 

 How to make a message box 6

 

To run the Procedure/Macro, go back to your Excel window. This can be done by either closing the VBA window or just minimising it.

 

On the Developer tab > Click on Macros and the new HelloWorld Procedure/macro will be in here.

 

How to make a message box 7

 

Highlight the HelloWorld Macro and click run. You will get a little message appear with your message inside!

 

How to make a message box 8

Microsoft Excel Tips: Absolute Referencing

Absolute Referencing is a forgotten tool in your Toolbox called Excel. Daily you will probably find yourself using the fill handle on excel to automatically fill tables. This is called Relative Referencing and this will change the formula/function in a cell depending on which way you drag the cell.

 

So the example below we have a Product List at a made up computer company.Absolute Referencing 1

 

 

As you can see in the Total Quantity Price column I used the fill handle to drag my formula down the formula to the other rows to fill in the column. Because I dragged the fill handle down the numbers row numbers increased 1 at a time.
Absolute Referencing 2

 

 

Absolute Referencing allows us to lock a formula in a cell. Let’s see this in another example.

 

I’ve extended my table and added a Price After Tax column. This column uses the 20% (or 0.2) number in cell H1 to add the Tax onto the Price. Using the $ before both the H and the 1 you are able to “lock” this cell down. So when you use the fill handle the cell wont change.

 

Instead of typing the ‘$’ in the formula you could press the F4 key on your keyboard to automatically add the ‘$’ to that cell reference.

Microsoft Excel Tips: How to Personalize your Table

When you use Excel the table can look really boring and plain; however in the newer versions (2007 and

Microsoft Excel 2007 Cell Styles Menu

2010) you are able to customize your tables by using different font styles and colours as well. There are also different shades of colour and sizes of text you can use. To change you table here’s how to do it:

 

  1. Click on the Cell Styles link on the toolbar
  2. This will give you a list of all the different styles that are available to you when making your table
  3. Click on the cell you want to personalize and then choose the style you want.

 

 

Want more tips; just visit our Microsoft Excel Tips page