Excel Training UK

Welcome to our Excel Training resource site full of Excel Tips and Excel Tutorials.
We also offer a range of Excel Training Courses in Brighton, Sussex.

What To Expect From Microsoft Office 15

Posted on May 4, 2012 by Andy Trainer

Microsoft Office 15 is likely to be released late in 2012, or early 2013, with a public beta likely to show its face this summer. Its official name is likely to be determined by the year of release (let’s hope for Microsoft Office 2012 then!).

microsoft-office-15-preview The current technical Preview beta version is private, and users have to sign a non-disclosure agreement. Bits and pieces have slipped out – even screenshots – suggesting very exciting changes indeed.

Paul Thurrott’s extensive screenshots and descriptions give you a thorough feel of the new developments – here are our top takeaways.

Look and Feel

The suggestion is that the Office 15 user experience will be a cleaner and more personal one.

As expected, Windows 8 design and concepts run through Office 15. Leaked versions run on the traditional desktop with a heavy influence from the new Windows Metro touch-screen style. A stepping stone, presumably.

The Ribbon

Love it or hate it, the ribbon (the one-touch UI of options, designed to avoid having to navigate through menus and toolbars) is still there – but hidden as default.

(Old version with ribbon)

Microsoft Office with Ribbon

(New version with hidden ribbon)

Hidden ribbon in Microsoft Office 2012 version 15

Integrated Design Options

Another first for Office version 15 is the introduction of a brand new tab for Design. This allows users to easily create and configure themes, borders, background colours and watermarks.

It’s likely that there will be extended video features for editing and broadcasting.

Cloud Integration

Microsoft Office 15 will automatically sign users into their Microsoft ID, including services such as Flickr, Windows Live Hotmail, Windows Live Messenger and SkyDrive. You can also set it to automatically connect to LinkedIn, SharePoint and Office 365.

When you select “Save As”, the top option will be to save the document to SkyDrive, followed by normal computer locations.

Microsoft Office 15 new features

Tablet users

Tablet users will benefit from the new full-screen mode which allows more viewing space beyond the part of your screen that contains the keyboard.

There’s also a specific ‘touch mode’ that increases the size of some buttons to make them easier to navigate by touch.

Power

Microsoft Office 2012 will use less memory and processing resources – which will certainly make it more accessible to tablet and notebook users.

Individual Applications

The majority of leaked updates and screenshots are focused on most people’s first stop – Microsoft Word.

The specific changes to the features of individual applications are, for the most part, yet to be seen. We’ll have to wait for the public Beta to be released in the summer to find out more!

(Images of Microsoft Office version 15 from winsupersite.com.)

Posted in Microsoft Office | Tagged microsoft office, Microsoft Office 15, Microsoft Office 15 preview, Microsoft Office 2012, Office 15, Office 15 preview | Leave a comment

How to Create a Dice Roller in Microsoft Excel 2007

Posted on April 27, 2012 by Andy Trainer

Use this Microsoft Excel tutorial to create your own dice roller, but why would you need to use Excel to roll your dice?

Excel Dice Tutorial

You’ve decided to have a family night in and play Cluedo. You open up the box, set up the game and go to start. One problem: you’re missing the dice. You frantically check all the other games; Monopoly, Yahtzee, Risk and even Battleships – which doesn’t even have dice. As usual, there are none to be found – they’ve all disappeared. Probably down the back of the sofa or under some boxes in the wardrobe.

For some, this means that family night is over. For others, it’s time to open up Excel and create their own dice roller!

How to create a dice roller in Microsoft Excel 2007

Step 1: Open up a blank spreadsheet in Excel.

Step 2: Select cell A1. From the Cells Menu within the Home Tab, select ‘Row Height’ from the Format drop down and enter 41. This makes all of Row 1 square cells.

Excel Change Row Height

Step 3: Enter the formula =INT(RAND()*6)+1 into cell A1.

Create Dice In Excel 2007

Step 4: Press F9 to roll your die, simply copy the formula into another cell if you want two or more dice. While you do have your dice working now, they don’t look very dicey, so here are some quick tips for formatting the cell. This is a great chance to show your kids (if you have any!) how to do some basic tricks in Excel. Read the rest of "How to Create a Dice Roller in Microsoft Excel 2007"

Posted in Excel Tips, How To | Tagged create dice excel, excel dice, excel how to, excel tutorial, microsoft excel dice roller tutorial, microsoft excel tutorial | 3 Comments

Excel Tutorial – How to Use the VLOOKUP and HLOOKUP functions

Posted on April 2, 2012 by Liza Baron

This is a simple tutorial in just one of many easily overlooked Microsoft Excel functions. If you find lots of gaps like these in your Excel knowledge then one of our tailor-made Excel training courses will be just what you need. We have public courses in Beginners Excel Training and Advanced Excel Training, and we can also tailor a private course to suit the individual need of you or your business.

VLOOKUP and HLOOKUP allow you to search a data range for any value entered, and return information or a value based on that data. VLOOKUP allows you to search information formatted by column, HLOOKUP for information formatted by row.

Read the rest of "Excel Tutorial – How to Use the VLOOKUP and HLOOKUP functions"

Posted in Excel Tips, Excel Training, How To | Tagged excel default settings, Excel functions, excel hints, excel tips, excel tutorial, HLOOKUP tutorial, microsoft excel tips, microsoft excel tutorial, VLOOKUP tutorial | 1 Comment

How to Create Drop Down Lists in Microsoft Excel

Posted on March 27, 2012 by Aaron Charlie

Sometimes, you just need to know how to do one thing in Excel and tutorial such as this is just what you need. However, if you find yourself always looking up how to do things and spending longer looking for solutions than actually using them then you should consider a Microsoft Excel Training course. Our courses are tailored for all ability levels; we run public Beginner Excel training and Advanced Excel training but can also tailor a course specifically to your requirements. Call us on 01273 622272 to discuss the level of training suitable for you.

Using a drop down list in Excel can make data input a lot easier – after all, you don’t want to waste time typing in the same thing again and again!

Preparing Your Excel Drop Down List Data

To start with, let’s make a list. Writing it out in alphabetical order can help, as the cell order is the order in which they will appear in the drop down list (the drop down list won’t automatically reorder itself). In this example, we’ll be using a list of flowers.

First, we write out our flower names in cells A1-A7:

excel-drop-down-list-tutorial-preparation

Creating the drop down list

Once your source has been created, you can start creating the drop down list. Highlight all the cells you want your drop down list to appear in (here, we are using cells E2-E20):

creating-an-excel-drop-down-list-

Next, click the data menu (at the top of the screen) and select validation. Scroll down on the “allow” list, and select “list”, like so:

excel-drop-down-list-data-validation

Next, click the source button:

excel-drop-down-list-source

and select our source list:

excel-drop-down-list-source-list

Finally, click OK!

You should now find that when you click any of the cells E2-E20 (or wherever you chose to place your drop down lists) that an arrow appears to the side of it:

Clicking on the arrow will make your drop down box appear!

excel-drop-down-list-tutorial-final

Now you are ready to use your drop down boxes, and won’t have to type anything too repetitive ever again!

Read more of our recent Microsoft Excel Tutorials:

Posted in Excel Tips, Excel Training, How To | Tagged drop down list tutorial, drop down lists, excel drop down list tutorial, excel training, excel tutorial, microsoft excel tutorial | 1 Comment

Microsoft Excel Tutorial: Scenarios

Posted on March 9, 2012 by Andy Trainer

This week’s Microsoft Excel tutorial is from one of our excellent Excel trainers, Jane. In this tutorial Jane shows us how to set up and show scenarios using Excel’s What-If tools.

For more in-depth training in Excel come to Brighton for an Excel Workshop. We have courses for all levels including:

If you want a course tailored to your needs then please call us on 01273 622272 as we also offer private and in-company training to suit you.

“The What-If Analysis tools in Excel enable the changing of values in cells to see how those changes affect the outcome of formulae. The Scenario Manager allows different scenarios to be created, for example to view results using different suppliers or for best and worst case sales results.

Tip: The Scenario Report will use cell references, so naming the cells that will be used first, will make you scenarios much easier to read.

Setting Up A Scenario

From the data tab, in the data tools group click what-if analysis.
Select scenario manager and click on ADD to add a scenario.
In the SCENARIO NAME box, enter a name for the scenario.
In the CHANGING CELLS box enter the cell address for the area you want to change or highlight it on the worksheet, then click OK.
To change the current input values, click into the relevant box and enter the new values.
Click on OK when you have finished.
If you want to add additional scenarios click on ADD.

Showing Scenarios

From the data tab, in the data tools group click what-if analysis, select scenario manager.
Select the scenario you want to use and click on the SHOW button and the cell values stored within the scenario will now appear on the worksheet.
Click on CLOSE.

We have plenty more tutorials so have a look through the Excel resources or search for something specific. If there is a tutorial you would like to see or a niggling question about Excel then please leave a comment below and we will try to help you out.

Read our last 4 Microsoft Excel Tutorials:

Guide to Microsoft Excel Pivot Table Slicers

Joining Cells in MS Excel Tutorial

Creating Gantt Charts in Excel 2007

How to Freeze Panes in Microsoft Excel

Posted in Excel Tips, How To | Tagged excel scenarios, excel scenarios tutorial, excel tutorial, microsoft excel tips, scenarios, what-if analysis, what-if analysis tool | Leave a comment

Excel 2010 Tutorial – Pivot Table Slicers

Posted on March 1, 2012 by Aaron Charlie

Pivot Tables are covered in detail on our Advanced Excel Training course. However, for some basic pivot table tips, read How to Create a Pivot Table and How to Change the Function of a Pivot Table. If pivot tables are a bit advanced for you and you’re looking to start afresh then our beginners Excel Training might be perfect for you. Click the link to read the full course description.

Pivot Table Slicers Tutorial

In a Pivot Table you can apply filters to your data, using the report filter and by filtering within the row or column fields. When using the multiple items in a report filter it is not obvious which criteria you have specified and likewise with the row and column filters.

So what can you do? The answer is to use Slicers in Excel 2010 and this is how to do it:

1. Ensure that you are not working in compatibility mode. (If you are convert your workbook first.)

2. Refresh the Pivot Table by clicking on the Refresh button on the PivotTable tools, Options tab.

excel-2010-tutorial-pivot-table-slicers-refresh

3. Click on the Insert Slicer button and select Insert Slicer.

excel-2010-tutorial-pivot-table-slicers-inset-slicer

4. Tick the fields you want to use to filter with, and a Slicer box will be displayed with items in the fields.

excel-2010-tutorial-pivot-table-slicers-insert-slicers

5. Specify your criteria by clicking on the item, or hold the Ctrl key and click to select more than one. The PivotTable will change to show only the data meeting your criteria.

6. To clear a Slicer filter, click on the clear filter button in the top right of the slicer box.

7. To remove a slicer box, click on the edge of the box and press delete on the keyboard.

Think pivot tables are a piece of cake? Want to take your Excel skills to the next level? Our VBA for Excel course is designed for experienced users of Microsoft Excel. Create and edit macros from scratch with comprehensive instruction on VBA code.

Posted in Excel Tips, Excel Training, How To | Tagged excel, excel 2010 tutorial, excel pivot table, excel pivot table slicers, excel slicers, excel slicers tutorial, excel training, excel tutorial, microsoft excel training, pivot table slicers, pivot tables, slicers, slicers tutorial | Leave a comment

Excel Function Keyboard Shortcuts

Posted on February 17, 2012 by Andy Trainer

Two posts into our run down of Microsoft Excel keyboard shortcuts and we’re getting stuck to the nitty gritty of functions, so now you have your navigation keyboard shortcuts and formatting keyboard shortcuts mastered you are prepared to take on some of Excel’s more specialised functionality and more advanced formatting.

Excel Keyboard

Using these key combinations you will be able to quickly summon functions which will hopefully be able to use to formulate, tabulate and graphically represent your data to give it meaning and context. Read the rest of "Excel Function Keyboard Shortcuts"

Posted in Excel Tips, How To | Tagged Excel functions, excel hints, excel shortcuts, excel tips, excel top tips, excel tricks, excel tutorials, formatting, keyboard shortcuts, microsoft excel, microsoft office, microsoft office excel, quick excel tips | Leave a comment

Microsoft Excel Formatting Keyboard Shortcuts

Posted on February 9, 2012 by Andy Trainer

Excel Keyboard Shortcuts After the popularity of our previous Microsoft Excel shortcut tutorial post we have decided to follow it up so you can be introduced to using keys for performing formatting. Hopefully you are now using your mouse far less after using our navigation shortcut guide. If not, be sure to have a run through of it first.

Many of these shortcuts can be used across many programs within the Microsoft Office suite, so they can be invaluable to learn in order to speed up your regular day to day tasks. Read the rest of "Microsoft Excel Formatting Keyboard Shortcuts"

Navigation Keyboard Shortcuts for Excel 2007

Posted on February 2, 2012 by Andy Trainer

Select All The Things Have you ever stared in awe as you watched a blur of fingers pull off tasks in Microsoft Excel at break neck speeds while you opt to complete similar tasks through using the mouse? The speed and accuracy experienced spreadsheet jockeys can achieve is no accident but through the picking up of keyboard shortcuts which can hasten the most complex of tasks.

But how do you leave the dependence of your trusty mouse and make the move over to becoming one of the Excel power user elite? Like anything it’s practice, but without knowing what to practice you may never get off the ground. This is why we have put together this handy tip sheet of some of our favourite keyboard navigation shortcuts for Excel 2007 to get you started. Once you have got the navigation down then we can get into some of the more meaty tasks such as functions, formatting and cell manipulations, but first give these a try and see if it speeds up your work. Read the rest of "Navigation Keyboard Shortcuts for Excel 2007"

Posted in Excel Tips, How To | Tagged excel, excel shortcuts, excel worksheets, keyboard shortcuts, quick excel tips, shortcuts, time saver | Leave a comment

Joining Cells in Microsoft Excel 2007 Tutorial

Posted on January 24, 2012 by Aaron Charlie

This tutorial is compiled using Excel 2007 but should work on all versions of Microsoft Excel.

Often when using Excel you want to keep values in separate columns but also present them together. For example, when compiling a list of names you would want:

First Name
Surname
Full Name

You may also then want to add in numerical data as well. Let’s say this is for a customer list you may want customer ID and date contacted. Now your columns look like this:

Excel-joining-cells-tutorial

First Name
Surname
Full Name
Customer ID
Date Contacted
All Details

Immediately you realise that all the data in the ‘Full Name’ and ‘All Details’ columns is already entered. Instead of typing everything out twice – which often results in mistakes – you can use an Excel function to join multiple cells together. Here is how to join cells containing text and numbers: Read the rest of "Joining Cells in Microsoft Excel 2007 Tutorial"

Posted in Excel Tips, Excel Training, How To | Tagged excel, excel 2007, excel tips, excel tricks, excel tutorials, how to join cells, joining cells, joining cells containing dates, joining cells containing numbers, joining cells containing text, joining cells excel, microsoft excel, microsoft excel 2007, ms excel tutorials | 1 Comment