Excel Advanced Course

Excel is a vital tool for boosting your efficiency and productivity when you’re dealing with large amounts of data and calculations. This course is a journey through the key topics for Excel power users and majors on Macros and Pivot Tables…

Microsoft Excel Advanced Course

Excel Advanced Course

Excel is a vital tool for boosting your efficiency and productivity when you’re dealing with large amounts of data and calculations. When you understand Excel at a more advanced level, you’ll have the ability to use its more sophisticated tools to streamline your workflow. Our Excel Advanced course is a journey through the key topics for Excel power users and majors on Macros and Pivot Tables.

All our courses are available online with Zoom or Teams. You are welcome to attend our training centre or we can come to you. Call 020 7920 9500 today for further details.

Macros

Create macros to automate your day-to-day Excel work. Save hours of your precious time otherwise wasted on repetitive tasks. Excel has its own command language, VBA (Visual Basic for Applications) We can use VBA to give instructions to get things done.

Specify a series of commands and instructions that you link together to perform tasks automatically. This is a great help with tasks such as saving and copying files from other sources and aggregating data. Get the Excel robot to work for you!

Pivot Tables

You can analyse massive amounts of data with Pivot Tables. On our Excel Advanced course we will examine some of the advanced Pivot Table features. See how to link different tables together with relationships and weave your magic with multi-table reports.

We can use Grouping to aggregate data and visual Slicers to filter our data by different views. Then we shall produce various metrics with Summarize By calculations and use Power Pivot to calculate measures using DAX formulas. We’ll round up the show by doing some serious data crunching with Power Query.

Stand Out. Get Certified!

In addition to our standard courses we also offer certified courses for those taking their MOS exams. MOS (Microsoft Office Specialist) certification is a world-wide recognised qualification that validates your proficiency in using software applications.

The MOS certified course is a 2-day event held at your office, hosted by a MOS certified trainer. You can take the exam online at your office if you wish. Or arrange the exam for whenever it works for you.

The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.

It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.

Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.

Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.

Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.

I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…

Essential Excel Functions

Functions are the backbone of most worksheets. But there are around 500 worksheet functions in Excel! Our course covers the most important Excel functions. The ones you will actually use time and time again…

Excel Essential Functions Course

Essential Excel Functions

Functions are the backbone of most worksheets. But there are around 500 worksheet functions in Excel. How can you possibly learn them all? The answer is, of course, you don’t need to learn all of them. But you do need to know Excel’s best ever functions. The ones you will actually use time and time again.

Our one-day training course is devised to give you a broad skill set covering all the primary categories of Excel functions. We will examine how to create filtering calculations using functions like SUMIFS and COUNTIFS. Then concentrate on introducing logical decision-making into our formulas. Make your formulas think for themselves! And, finally, master the use of lookup functions like VLOOKUP.

All our courses are available online with Zoom or Teams. You are welcome to attend our training centre, or we can come to you. Call 020 7920 9500 today for further details.

Filter Functions

Everybody can do a SUM or a COUNT. But, in real life, many of our calculations require some type of filter. Add up all the sales between two dates. Count the number of deals for a specific product. These are examples of filtered calculations. And this where SUMIFS and COUNTIFS are invaluable. Some people would say that you can’t do without them, they are essential functions.

We’ll also deal with the legacy functions, SUMIF and COUNTIF and take a quick look at some of the other handy filtering functions like AGGREGATE and SUMPRODUCT.

Logical Functions

One of the best Excel skills to master is the ability to make your formulas intelligent. Then you’ll start to feel that Excel is working for you. And not the other way round! We shall see how to use functions like IF, IFS and SWITCH to get our formulas to follow a set of simple rules and return different results based on logic.

Mastering logical functions completely changes your formula game. We’ll start by considering the basic if-then-else logic. And then see how to make multiple tests, combine different tests together and how to force out ugly error values.

Lookup Functions

Many people first discover lookup formulas in Excel worksheets that they’ve inherited from their colleagues. Then they realise, with a sense of horror, that these formulas are driving most of the calculations in the worksheet. And they can’t make head nor tail of them!

Don’t let functions like VLOOKUP or MATCH work their mystery on you. Have you ever looked up the price of a Flat White in your local coffee shop? Yes? Then you already understand what VLOOKUP does. Spend a bit of time with us and learn how to use these all-important and essential Excel functions. It’s time well spent.

Microsoft Power BI Courses

The aim of BI (business intelligence) is to make sense of the vast amounts of data that organisations hold. Our Power BI courses help you analyse and visualise your data and thus guide effective business decision making…

Microsoft Power BI Courses

The aim of business intelligence (BI) is to make sense of the vast amounts of data that organisations hold. Our Microsoft Power BI courses help you analyse and visualise your data and thus guide business decision making and management. We provide Power BI courses from beginner to advanced levels. Our courses can be tailored to best suit your requirements.

All our courses are available online with either Zoom or Teams. You are welcome to attend our training centre, or we can come to you. Call 020 7920 9500 today for further details.

Power BI Introduction

Our Microsoft Power BI Introduction training course helps you extract, analyse and comprehend large quantities of data. This will ensure that you can present data in a logical manner and make informed business decisions. Microsoft Power BI (Business Intelligence) is a user-friendly, report-based analytical tool that is transforming the way that businesses read their performance data.

The course starts from first principles and assumes no previous knowledge. You will learn how to link and model your data in Power BI and then create and share visual reports that reveal business insights.

Power BI Advanced

Our two-day Microsoft Power BI Advanced training course is designed for those who are already using Power BI. But they need to explore its features in more depth. It is ideal for business or financial analysts, data scientists and staff.

Learn about the Data Model, its structure and function and how best to share it. Work with more advanced DAX functions to effectively manipulate your business data. See how to use complex Queries for data load. And use bookmarks and buttons to develop an interactive visual interface.

Mastering DAX

Our two-day Microsoft Power BI Mastering DAX (Data Analysis Expressions) training course gives you a deeper understanding of this powerful data manipulation language. Although it’s often described as “simple but difficult”, DAX is a simple language. But it can be quite a challenge to do the calculations you actually need! However, once mastered, effective DAX formulas are the key to getting the most out of your data.

Our course covers how to use DAX to join and generate tables, control table relationships, gain an understanding filters and context and how to use Time Intelligence calculations.

Excel Pivot Tables Course

Pivot Tables are one of Excel’s most powerful features. And one of the easiest to learn! You can summarise and analyse huge amounts of data easily and rapidly. Produce calculations, slicers, dynamic reports and a whole lot more…

Microsoft Excel Pivot Tables Course

Excel Pivot Tables Course

Pivot Tables are one of Excel’s most powerful features. And one of the easiest to learn! You can summarise and analyse huge amounts of data easily and rapidly. Take our one day Pivot Tables course to go from zero to hero. We start from first principles and go on to produce calculations and dynamic reports. You don’t need to know anything about Excel formulas to attend our Pivot Tables course.

All our courses are available online with Zoom or Teams. You are welcome to attend our training centre, or we can come to you. Call 020 7920 9500 today for further details.

Create Great Pivot Table Reports

How are you doing your regular reports? Crunching numbers with calculators and hundreds of bits of paper is no fun. And it’s always against the clock. We’ll show you how to do it with Excel Pivot Table reports. Update your source data, click to refresh your analytical reports and you’re done for another reporting cycle. Easy!

But there’s more to Pivot Table calculations than just a simple SUM or COUNT. We’ll demonstrate the use of calculated fields and items and how to take advantage of the Show Values As options to calculate percentages, comparatives and differences. These are great for showing calculations like variance on previous month or market share etc.

After you’ve crunched your numbers, we’ll show you how to help everyone interpret your data by introducing data graphics like Slicers, Timelines, Charts, Data bars and Traffic lights.

Keep Your Excel Skills Up to Date

There’s been quite a few changes to Excel in recent years and they’re easily missed. Our New Functions course covers functions like XLOOKUP, FILTER and many more. Number crunchers should check out our Power Query course…

Keep your Microsoft Excel Skills Up to Date

Update Your Excel Skills

Excel is one of the most used office applications. At the same time, it’s one of the most complex. And with so many different disciplines to master: formulas, charts, macros, pivot tables and so on, it’s easy to miss something crucial. Or something that’s new! Try taking a look at our courses and keep your Excel skills up to date.

Our Excel 365 New Functions course showcases all the new functions that have been appearing in Excel in recent years and demonstrates what you can achieve with the new Dynamic Array formulas.

Our other featured course is Excel Power Query. Power Query has been available in Excel for the past few years but falls into the “I never realised that it was there!” category. It’s a crying shame that so many people have not been taking advantage of Power Query to crunch their worksheet data. We hope that our course will give you a few ideas. And save you hours of work.

All our courses are available online with Zoom or Teams. Or you are welcome to attend our training centre. Or we can come to you.

Excel 365 New Functions

Our Excel 365 New Functions training course gets you bang up to date with the quiet revolution that’s been going on with Excel formulas recently. Most of us have relied on familiar functions like VLOOKUP and nested IFs for years. That’s now had a major overhaul. Update your Excel skills.

Don’t worry, all the original functions are still supported. But new functions like IFS, LET and XLOOKUP will completely change your formula game. Making them more powerful and efficient.

Excel’s new calculation engine introduces a new type of function, Dynamic Arrays. We can do calculations that we’d never even thought about doing before. Because now you can easily work with multiple values at the same time in a formula. Dynamic Arrays solve some very tricky problems in Excel formulas and fundamentally change the way our worksheets are designed and constructed.

Excel 365 Power Query

Our Excel Power Query training course is ideal for you if you have to spend time every month cleaning and transforming data. And that’s before you can even begin to analyse it and incorporate it into your reports. Stop the eternal copy and paste, automate your workflow!

We show you how to import data from many different sources. Then combine, transform and reshape your data as required for your Pivot Tables and other Excel reports. You set up your query once and then reuse it with a simple refresh. It’s like a macro, but without the code! For years so many of us have had to write VBA macros to manipulate our data, now you can update your Excel skills by discovering the power of Power Query. Power Query is built into Excel.

Mouse Training and Prodigy Learning- a Success Story

Microsoft Office Specialist Certification

Microsoft Office is No.3 in the Top 10 Most Sought-after Employability Skills according to a recent IDC Study. Mouse Training London, a leading IT Training provider in the UK wanted to meet the growing demand from its customers to offer official Microsoft Office Specialist (MOS) Certification that is recognised and respected by industry. Microsoft MOS Certification was embedded when Michael Holford, Managing Director, Mouse Training London recognised the challenge many companies faced of a gap in validating new recruit’s skills and expertise in Microsoft tools.

Mouse Training London initially offered MOS Certification Exam Packs at its testing centre. The company then realised its role in preparing students for certification was much more important to ensure candidates digital skills matched those demanded by employers. To deliver on this role Mouse Training London embedded a full comprehensive MOS Certification programme and has since seen pass rates greatly improve.

Benefits of MOS Certification to Test Centre Candidates

Microsoft Office Specialist (MOS) Certification is a formal qualification that makes Mouse Training London’s students stand out as valuable and knowledgeable individuals. Embedding the MOS Certification programme is the best way for candidates to develop the crucial digital skills required by today’s employers and to prove competency and efficiency in using the software tools that businesses need workers to use daily.

Proving competency with Microsoft software is becoming more relevant than ever, as many employers are doing job interviews virtually and reducing the onboarding time for new hires. Mouse Training London is delighted to offer the cachet of a leading IT vendor like Microsoft which draws learners from all walks of life enabling them with the skills to stand out from the crowd and offer a recognised proof of their competence.

Michael Holford, Managing Director, Mouse Training London Tweet

It has been a great pleasure to work with Mouse Training London over the past few years to embed the Microsoft MOS Certification Program. This adds great value and enables Mouse Training London to remain a leading IT Training Provider, helping organisations get the most out of their technology investment by improving both efficiency and productivity. MOS Certification develops employee’s proficiency and accelerates new Microsoft technology deployments by reducing downtime.

Jonathan Cody, Senior Account Manager, Prodigy Learning

Solving the lack of Digital Skills for Employers

The credentialization of workplace skills is a growing phenomenon today. And rightly so, would you let an unqualified vet operate on your cat? MOS Certification offers candidates the opportunity to prove to employers that they have been recognised by a leading vendor as having skills in these areas.

Michael Holford, Managing Director, Mouse Training London Tweet

Certification proves candidates have a fantastic work ethic, the ability to focus on developing valuable skills and achieving globally, industry-recognised certifications. Exam takers are doers and achievers, certification is the validation of their endeavour. Having certified employees to a company increases overall productivity and ensures the company has the relevant digital skills to remain competitive.

Certification to Secure Senior Position in Leading Supermarket Chain

Mouse Training London have seen many examples where having MOS Certification on CV’s and LinkedIn professional profiles has certainly helped candidates in securing employment. Pre-Covid, one of our students travelled from Birmingham to our London centre five Saturdays in a row to gain his MOS Expert Certification. A few weeks later, he was appointed to a senior position at a major national supermarket chain. Most of Mouse Training’s candidates who are already in employment report MOS qualification boosts their confidence, improves productivity, and can lead to increase in salary in comparison to non-certified peers.

Digital Badging to Prove Skills to Employers Globally

Successful MOS candidates value their industry-recognised digital badges as they can display their achievement online, using LinkedIn, Facebook, and Twitter to employers around the world. For employers, knowing the digital badges link through to a trusted source and are verified officially by Microsoft, gives confidence that the candidate has the required skills.

Earning Microsoft Certification at Home during Global Pandemic

In these challenging times, and to comply with social distancing regulations we have had to limit attendance at its testing centre. The Exams from Home Certification Solution has proved to be enormously popular and enabled our candidates to continue to prove industry-demanded skills from home. In fact, Mouse Training London are hosting more certifications online compared to previous years when candidates had to physically attend the training centre.

For more information on Microsoft Office Specialist, click here.

About Prodigy Learning

Prodigy Learning is an award-winning global EdTech business, providing innovative online platforms that enable learners to develop and prove their skills. These solutions range from skills assessments in education through to job-ready digital skills certifications from IT industry leaders including Adobe, Autodesk and Microsoft.

The Company was established in 2000 and now has offices in Dublin, Ireland, London, UK, Sydney, Australia and New York, United States. Prodigy Learning has worked closely with Microsoft since incorporation in 2000. The Company is a Microsoft Gold Application Development Partner and a Global Training Partner of Microsoft in Education. To learn more, visit www.prodigylearning.com.

Word Doing it with Styles

Word Styles

Word, doing it with styles. Do you spend ages formatting the text in your Word documents? If that’s a “yes” then the bad news is that you’ve possibly been spending far more time than you need to. The good news is that you can save a huge amount of time doing your formatting if you get into the habit of using Styles. And there are many other benefits, as we shall see…

Applying Styles

All of the text in a Word document is in the Normal style. The format of the Normal style is defined by the document template, usually the Normal template. If you are not used to using styles then you don’t bother with any of this. You just select your text and change its format. That’s fine as far as it goes but you’re not really getting the best out of Word unless you use styles. The fundamental way to format text in Word is to apply a style, particularly where you have headings and subheadings.

Heading Styles

To apply a style to your headings and subheadings, select them one by one and apply a Heading Style from the Styles Gallery on the Home tab.

Use the built-in Heading styles that you can see in the gallery: Heading 1, Heading 2, Heading 3 etc.

Don’t worry if you don’t like the look of your headings because you can always change them later. Not by changing their format but instead by changing the definition of the heading style. Once a style has been applied you can change it as many times as you like. And in as many different ways as you like.

To change the appearance of all the body text in your document you modify the Normal style. To change all the main headings, you modify the Heading 1 style etc.

The built-in heading styles go from Heading 1 to Heading 9 and you can apply them using shortcut keys. The shortcut keys only go from ALT+CTRL+1 for Heading1 to ALT+CTRL+3 for Heading3. Three levels of headings is usually enough for most documents. The key combination CTRL+SHIFT+N to apply Normal style is a good one to know for when you apply a heading style by mistake.

Word formats everything with styles. You can usually see where they have been applied just by looking at the text. There is a very handy document view, Draft View. Many people prefer the Draft View to the usual Print Layout View as it lets them see exactly which style has been applied to each paragraph.

When your styles have been applied you can modify them as you like. If you prefer the appearance of your styles to the standard template styles then you can change the default. Then your text looks exactly as you want it to on every new document.

Draft View

Word’s Draft View is designed to show only the text formatting and gives a simplified view of the layout of the page. You can type and edit swiftly in this view. Most people don’t bother with it and much prefer the standard Print Layout view. The draft view is invaluable when you need to see exactly what’s going on with your styles.

Usually you can tell by the appearance of a heading that you have styled it.

What you can’t see are your mistakes, such as where you have managed to apply Heading 1 to some white space. Or maybe you’re just a control freak. Do you always have that urge within you where to need to know exactly what’s happening?

To show the view, click the Draft control on the View tab. Your style definitions are not normally shown in this view. You must change a Word Options setting in order to see them properly. When you have changed the property, the styles are shown on the left hand side. You only ever have to do this once.

Set the width of the Style Area Pane

To set the width of the style area pane, click the File tab, Options, Advanced, Display section and locate the Style area pane width in Draft and Outline views: control. Enter a suitable width, around an inch or 2cm is about right.

Modify your Styles

Very few people find the appearance of the built-in styles suitable for their purposes. All you have to do is Modify your built-in styles to get exactly the font, paragraph and other formatting that you want.

Then you can either change the Word defaults so that you never have to do this again. Or create a single Style Set which you can apply to the whole document in one step.

The whole point about styles is that you never actually do any formatting apart from applying a style. When you change the properties of the style then everything with that style in your document gets changed.

Changing the Normal Style

For example, many people like to change the font being used in a document and do this by selecting all the text and then choosing a different font from the font control.

This works fine but paragraphs have over a hundred different properties and if you then wanted to say, change the line spacing as well, you would have to go through the whole selection routine again. And then you change your mind…

The alternative and much easier method is to modify the Normal style. Right-click Normal style in the Styles gallery on the Home Tab. In the illustration we changed all the body text in the document with a few clicks in the Modify Style dialog. Change the font style, size and justification using the controls in the Formatting section (1). Then click the Format button (2) to change the paragraph formatting.

Changing the Heading Styles

Repeat the same process to change the appearance of the heading styles. Don’t forget to click on the text where the heading style has been applied first before you modify it. There’s nothing more annoying than changing a paragraph of body text to Heading 1.

You don’t like the look of the twenty or so headings in your document? Click on one of them and modify the heading style. The change goes through the entire document.

Or would you prefer to do twenty selections and around another forty formatting clicks to change all your headings? No contest.

Benefits of using Styles

Applying styles ensures speed, consistency and stability in your document. Speed, one change to a style ripples through the document irrespective of how long it is. Consistency, everything formatted with a style complies with the style. Were my main headings 18pt or 16pt? If you have to do them individually then you’re bound to get some of them wrong. Stability, you can not delete an in-built style. Under certain circumstances it may reset to its default values but it’s always there.

Saving your Styles

After all that hard work modifying your styles you will not want to repeat it. If you know exactly what you want for all new documents then change the default. This changes the standard Word document template on which all new documents are based. It does not change documents that you have already created.

Click the Change Styles control on the Home tab and choose Set as Default. Alternatively, you can create a Style Set which consists of a collection of style definitions. Then you can have your own collection of Style Sets which you can apply to different types of document.

Create a Style Set

To create a Style Set, click the Change Styles control, choose Style Set and Save as Quick Style Set from the fly-out menu. Enter a suitable name for your style set and Word saves it as a template. Whenever you want to apply the style set, click anywhere in your document and choose the style set from the list displayed.

Cleaning Up Existing Documents

Usually it’s best to do all of your document formatting with styles but that’s not always possible. Sometimes you have a document that you’ve already started formatting or, more likely, you inherit documents from other people. These documents can be an amazing mixture of all sorts of styles and fonts. What you want is a nice, clean document that you can format from scratch.

Select all of the text in the document by pressing CTRL+A and then press CTRL+Space to remove any character formatting (colours, fonts, bold, italic etc.) Finally, press CTRL+Q to reset any paragraph formatting (alignment, space after etc.)

All the paragraphs revert to their default values. Any heading styles used will remain in place but all the additional formatting is removed.

Using your Styles

Identifying your body text and your different levels of headings gives your document a structure. A structure that Word can use for a variety of useful tasks. The following sections are a brief guide to how Word can use your styles. And make it well worth the effort of applying them.

Navigation Pane

If you’re sick of having to scroll up and down through long documents then you will really appreciate Word’s Navigation Pane which opens on the left hand side of the Word window. It gives you a bird’s-eye view of the headings in your document.

To show the Navigation pane, either click the relevant checkbox in the Show group of the View tab or press the shortcut key CTRL+F. Click the leftmost icon tab in the pane to browse through your headings.

Using the Navigation Pane

Click one of the headings in the Navigation pane and you move to that place in the document. This alone would make me happy but you can also reorganise your work in this pane by dragging and dropping the headings.

The two other tabs in the pane show either thumbnails of each page or Search.

A Table of Contents (informally known as a “TOC”) is a summary list of the contents of your document with numbered page references. It is typically included after the title page. You can easily generate a TOC based on your heading styles, click in your document where you want to insert the TOC and then click the Table of Contents control on the References tab and choose one of the Built-in tables.

The Table of Contents is a snapshot of the current state of the document. When you update your document don’t forget to update your TOC to reflect the changes you have made. Click anywhere in the TOC, the entire table is selected and shaded (it’s a Word field) then click the Update Table control or press F9.

TOC styles control the appearance of the TOC and you can modify them if you don’t like the look of your TOC. They are available in the Styles Window, to see this window either click the dialog launcher at the lower right of the Styles group of the Home tab or press the keys ALT+CTRL+SHIFT+S. When you can see your TOC styles, right click or click the drop-down arrow on the style. Choose Modify from the shortcut menu.

Hyperlinks and Cross-references

Hyperlinks are those underlined and coloured words that you see in web pages. When you click the link you jump to another page or location. You can insert them into Word documents to jump to a web page, an email address or, more commonly, a Place in This Document.

Creating a Hyperlink

Word Hyperlinks create a convenient navigation structure for anyone reading your document. To create one, click the Hyperlink control in the Links group on the Insert tab. Click Place in This Document and you will see all your headings listed on the right hand side. If you don’t want to link to a heading then you must create a Bookmark first and then link to it. Your Bookmarks will be listed under the headings.

To create a Bookmark select some text in your document and click the Bookmark control. Enter a name for the Bookmark and click the Add button. Bookmarks are place holders in the document, you can’t actually see them unless you choose Options, Advanced, Show Document Content.

Creating a Cross-reference

Word can create Cross-references to your headings or bookmarks which can be updated as the document changes. For example, we have some heading text, “Geography” to which we have applied the Heading 2 style and we wish to create a cross-reference that reads like this, “See Geography on page 4“.

Click where you want the cross-reference, type in the word “See” followed by a space. Then click the Cross-reference control.

Choose Heading from the Reference type list and Heading text from the Insert reference to list. Click the Insert button. That gives you the “Geography” bit and it will update should you subsequently change the text of the heading. Finish off the cross-reference by typing ” on page ” and then repeat the process but this time choose Page number from the Insert reference to list.

Updating your References

Whenever you want to update your cross-references and make sure that their information is up to date, select the entire document by pressing CTRL+A and then press function key F9 to update everything.

Paragraph Numbering

Word heading styles are ideal when you want sequential paragraph numbering in a hierarchy that goes something like this: 1, 1.1, 1.2, 1.3, 2, 2.1, 2.2 etc. What you need is a List Style which will group existing heading styles and relate them to each other. Fortunately, Word contains a library of built-in List Styles which are easily applied and you can also create your own to suit.

Using a List Style

Create your document and apply the various heading styles to your headings as usual. You don’t actually do any numbering yourself, instead you let the heading styles and the List style apply the numbering for you.

Select all the text in your document by pressing CTRL+A and then click the Multilevel List control in the Paragraph group on the Home tab. Or just click at the start of the document, there’s really no need to select any text once your styles are applied. Browse the List Library and select one of the Lists which are clearly based on the heading styles and Word will number all your paragraphs automatically.

If you can’t find a List Style that works for you then you will have to create your own. This is not the easiest of tasks but it’s the only way if you want something special. Click Define New List Style in the Multilevel List menu.

In the Define New List Style dialog, enter a name for the list then click the Format button and choose Numbering. Click the More button and then link each list level to your heading styles and set the number style and formatting.

Outline View

Word’s Outline View is often neglected. This is a shame as it is the best tool for the job when you are having to manage long documents such as reports, proposals, contracts etc. These documents usually have several sections and require extensive editing and reorganisation before the final version is produced.

Create your document as usual and apply heading styles to the headings. Click the Outline control on the View tab to turn on the Outline view.

In this view you see a condensed version of the full document showing the normal body text paragraphs and the headings shown at their various levels. Heading 1 style corresponds to Level 1, Heading 2 style corresponds to Level 2 etc.

It is now so easy to reorganise your document, all you have to do is drag and drop a heading to move the heading and all its associated subheadings and body text. There’s absolutely no need for all that selection and cutting and pasting work that so many people inflict upon themselves.

When you have set up automatic paragraph numbering you will see that all the numbering updates to comply with the new structure. You can use shortcut keys if you don’t want to drag and drop the headings. Click a heading and then press ALT+SHIFT+Up Arrow or ALT+SHIFT+Down Arrow to move that section up or down in the document.

Creating a PDF file

Word’s built-in heading styles are easily interpreted as PDF bookmarks. One of the most useful additions that you can make to most PDF’s is to create bookmarks for navigation. PDF bookmarks are the clickable objects shown on the left of the Adobe Acrobat window that you use to expand and collapse the headings and show the different levels. Much the same idea as Word’s Navigation pane.

Creating bookmarks manually is a very painful task. But there is no need to bother with that as you can set the built-in headings in your Word file as PDF bookmarks when you save the document as a PDF file. Click the Options button in the Publish as PDF or XPS dialog and check the checkbox Create bookmarks using and the option button Headings.

Doing it with Styles Conclusion

That’s the end of the Word styles propaganda. As we’ve seen, Word formats everything with styles. But it’s not just document formatting where styles can help us get our work done efficiently, there’s quite a few other things a well. If you’ve been using Word for a bit and you’ve not yet tried using styles, give them a go and see if they work for you.

Related Courses

Microsoft Word Intermediate – Link

Microsoft Word Advanced – Link

Excel Converting USA Dates to UK Dates

Converting USA Dates to UK Dates

Excel Converting USA Dates to UK Dates. Usually Excel date values don’t cause any problems, you just type them in with either slash or dash separators. Don’t bother about the year value unless your date is for a year other than the current year. Excel automatically enters the current calendar year if you just give day and month values.

Country Setting for Dates

However, you will have a problem with the date evaluation if the country setting on your system has not been set correctly. Excel was written in the USA and uses their Month-Day-Year date convention. You will have to change the country setting if you want to use the UK convention of Day-Month-Year.

The United States Declaration of Independence is clearly dated “July 4 1776” so there’s very little chance of their changing the date convention. You can’t argue with the Founding Fathers.

The system country setting issue is easily resolved. Just Google Change the regional settings if you’re not sure how to do it. The real date problem arises when you have to share documents with your friends and colleagues from across the pond. Your opposite number in New York emails you data that was quite sensible when it was entered. When you open the document the dates go haywire.

Reversing the Date Evaluation

You can Google formulas that will reverse the date evaluation for you but they are awkward to implement if you have to do this sort of thing regularly. In this article we are going to create a Date Converter that reverses the day and month values of any date directly in the cell without having to use formulas.

The converter uses an Excel macro which is triggered when you click a shortcut button on your Quick Access Toolbar (QAT). You click the button, select the range of cells containing your dates and you’re done!

Converting UK Dates to USA Dates

It also works the other way round. So, if someone from the London office sends you a spreadsheet and the dates are entered in good-old-King-George Day-Month order you just select your cells and click your shortcut. The date evaluation is whizzed around into nice, sensible Month-Day dates.

Making the Date Converter macro

There’s ten steps to go through here but don’t let that put you off as they are very simple and the entire process will only take a few minutes. If you’re not familiar with the term “macro” then allow me to explain. Macro is short for macro-instruction and is a sequence of instructions written in Excel’s scripting language, VBA (Visual Basic for Applications)

Step 1. Recording a macro

The first step is to record a macro into your Personal Macro Workbook. This is a hidden workbook that is opened automatically whenever Excel starts up, any macros saved in this workbook will always be available for you to use.

Go to the Macros control which is found on the extreme right-hand side of the View tab on the Excel ribbon. Click the bottom section of the control and choose Record Macro from the menu.

Step 2. Using the Personal Macro Workbook

There is very little to do here other than to make sure that you record a macro in the right place.

When the dialog is displayed, change the Store macro in setting to Personal Macro Workbook. It’s available in the drop down list. Don’t bother with anything else. Click OK to start the recorder.

Whatever you do now gets recorded but there’s no need to do anything. Just turn off the recorder and this step is completed. Click Stop Recording in the menu on the Macros control.

Step 3. Finding the recorded macro

We have now created a module (Excel’s storage area for macros) in the Personal Macro Workbook. The next step is to find this module, remove the recording and replace it with the instructions that will do the date conversion work for us.

This is done in the Visual Basic Editor. Press ALT+F11 to open the editor and then see if you can spot a window in the top left-hand corner which looks like this illustration. That’s the Project Explorer window. If you can’t see it, press Ctrl+R.

Look in the listing under PERSONAL.XLSB, you may have to click the plus sign nodes to open up the listing. If you’ve never used the Personal Macro Workbook before you will need Module1. If there are a set of modules visible then you need the last one. Double-click the module and you will display the code of your recording in the window on the right-hand side of the screen.

Step 4. Copying and Pasting the code for the macro

Your recorded macro code will look something like this illustration. Don’t worry if it doesn’t as we are going to delete it and substitute our own code. Select all your code from the word Sub to the words End Sub and press the DELETE key.

Then copy and paste the code from the section below to replace the original.

This is the code for the date converter macro:

Public Sub DateConverter()

Dim rngCells As Range

Dim rngCell As Range

Dim strMsg As String

Dim intDay As Integer

Dim intMonth As Integer

Dim intYear As Integer

Dim DateValue As Date

On Error Resume Next

strMsg = “Select the cells to convert:” & _

vbCr & vbCr & “Reverses Month and Day date evaluation,” _

& vbCr & “i.e. MM-DD becomes DD-MM if possible.”

‘Receive the input.

Set rngCells = Application.InputBox(strMsg, “Date Converter”, , , , , , 8)

‘Test for no input received.

If Not IsObject(rngCells) Or rngCells Is Nothing Then

GoTo Exit_DateConverter

End If

‘Date Conversion Loop.

For Each rngCell In rngCells

If IsDate(rngCell) Then

intDay = Day(rngCell)

intMonth = Month(rngCell)

intYear = Year(rngCell)

DateValue = intMonth & “/” & intDay & “/” & intYear

rngCell.Value = DateValue

End If

Exit_DateConverter:

End Sub

Make sure that you copy and paste everything, starting with the word Public and ending with words End Sub. When you’ve pasted the code you will see that some of the words will turn a blue colour and some green. That’s exactly what they should do.

Step 5. Saving the macro

Now save the workbook. PERSONAL.XLSB is a hidden workbook so save it now as it’s tricky to do it later. Choose File, Save from the main menu.

The macro is completed and we now get back to Excel by pressing ALT+F11 again. Or you can close the Visual Basic Editor window.

Step 6. Creating a shortcut for the macro

In the next few steps we shall create an attractive, easy to use shortcut for the macro.

The most obvious place to have this is on your Quick Access Toolbar. Point to the QAT (top left-hand corner of the Excel window), right-click and choose Customize Quick Access Toolbar from the shortcut menu.

Step 7. Customizing the Quick Access Toolbar

Excel’s Options dialog will display and the Quick Access Toolbar section is activated.

Working from the top left-hand corner of the section, click the Choose commands from drop-down list.

Then click Macros in the list.

Step 8. Assigning the macro to the QAT

Your macro will be displayed in the list on the left-hand side. Select the macro and click the Add command button.

Your macro now appears in the right-hand list which shows you all the shortcuts available on your Quick Access Toolbar.

The next step is cosmetic and will make your shortcut button more visually attractive. Click OK now if you want to skip this final step.

Step 9. Modify the macro button

Click the Modify command button if you want to change the icon displayed for your macro and change the descriptive ‘Screen-Tip’ that pops up whenever you point at the icon.

There’s an array of different icon images to choose from. Enter some text into the Display Name box to set the Screen-Tip text.

You don’t have to enter “Date Converter”, you can enter anything you like. Click OK to close the dialog and OK again to close the main Options dialog.

Step 10. Test the macro

Everything’s done but you always want to test it to make sure. Enter a few dates into your worksheet. Click the Date Converter button on your Quick Access Toolbar. Select the date cells when the input box appears. Click OK and all the dates get switched around.

Using the Date Converter

This is the input box that appears when the shortcut is clicked. Your current cell selection in the worksheet is not detected. Instead, you point out of the box and select a range of cells or a column on your worksheet. All the dates in the selection have their month and day values reversed. Anything that is not a date is ignored.

Congratulations on a successful result and I hope that you enjoyed the Excel converting USA dates to UK dates article.

Related Courses

Microsoft Excel Advanced – Link

Microsoft Excel VBA Introduction – Link

PowerPoint Removing Backgrounds from Pictures

Removing Backgrounds from Pictures

PowerPoint removing backgrounds from pictures. Extracting the main subject from the background of a picture or photograph used to be a marathon task (for me!) involving Photoshop masks and paths or background erasers but nowadays you can do it directly in process using the Remove Background control in PowerPoint.

You won’t get quite the sophistication or fine control of the Photoshop process but for simple images the edge detection is really good and it only takes a few minutes.

This is perfect for simple image manipulation, like removing the white background from a company logo.

Here’s a photograph of our mascot, Elvis, sitting on the bonnet of a car. He loves doing this while the engine’s still warm. But I didn’t want him on the car, I wanted him on a slide. PowerPoint will do this in 5 minutes, in real life there’s no way that cat’s moving. The first job is to insert the photograph onto your slide as you usually do and keep it selected.

Next, find the Remove Background control, it’s on the extreme left-hand side of the Picture Tools tab. Give it a click and see what it comes up with.

You usually find that the initial results are quite impressive and after a wee bit of tweaking you’ve got an excellent extraction.

First Pass

As you can see with this one most of it’s good but part of the car has been retained and Elvis has lost his stripey tail.

There’s a bounding box around the captured subject image and all the areas of the photograph that are to be removed are coloured mauve. Now for the tweaking. We’re going to adjust the bounding box to include the tail and then zoom in on the other areas and use the controls on the ribbon to specify which parts we need to keep or remove.

I’ve dragged the left edge of the box to the left and Elvis gets his tail back. Great. I’ve dragged the bottom edge of the box down and Elvis gets his front paw back but I’ve gone too far down and included his reflection on the car.

Keep going with the box until you get as close as you can to your desired image. Now it’s time to zoom in and deal with all the fine work by using the keep and remove controls on the ribbon.

Fine Tuning the Image

Zoom in or out of your image either by using the Zoom control at the lower right-hand corner of the PowerPoint window or by holding down the CTRL key as you spin your mouse wheel forward or back.

When you use the Mark Areas to Keep and Mark Areas to Remove controls don’t bother trying to draw a line around the areas to keep or remove, instead draw a line straight across them and let the edge detection do the work for you.

Draw a line with the pencil pointer straight across the offending areas.

Here we can see the results, after a few swift strokes most of the car has been removed and if I had more patience then I would have continued going around Elvis and tidying up his fur but I wasn’t bothered as it’s a fairly low-res photograph and the intended slide image is quite small.

It’s time to click the Keep Changes control and let the cat out of the bag.

I’ve often wondered why that cat is called Elvis.

Finished

And finally, here’s the old fella on his slide ready to wow his audience by telling them all about the really important things in any cat’s life.

I’ve changed the background image and I’ve flipped and rotated Elvis. He won’t like that at all.

“Where’s my dinner?” “Miaow!!!” I think it’s time I left the building. Best of luck with your PowerPoint removing backgrounds from pictures.

Related Courses

Microsoft PowerPoint Introduction – Link

Microsoft PowerPoint Advanced – Link

Excel Calculations without Formulas

Excel Calculations Without Formulas

Excel Calculations without Formulas. If you use Excel on a regular basis then you probably know all about formulas and functions but it’s too easy to get into a mental rut and neglect some of Excel’s simpler operations. Here’s a typical example, my worksheet contains the Sales Forecast figures for the next few months and I’ve just been told that I now have to increase all the figures by 4%.

So what do you do? Copy and Paste all the numbers to another worksheet, write a formula to multiply everything by 1.04 then Copy and Paste Special as Values to fix the numbers and finally, copy all the new numbers back to the original worksheet. Well, you could but….

Paste Special Operations

Instead of removing the numbers to another worksheet and doing formulas you can manipulate the cell values in place. Type the multiplier value of 1.04 into an empty cell (any cell will do) and then copy it. Now that you have your value on the Clipboard you can apply it to the numbers.

Select your numbers and then choose Paste Special and in the Operation section click the Multiply option button. Click the OK button and all your numbers are uplifted by 4%. You don’t need the 1.04 multiplier in the cell any more and you can delete it whenever convenient.

Halving numbers, doubling numbers, converting negatives to positives (multiply by minus 1), adding one set of numbers to another or subtracting. These tasks can all be effected without writing a single formula. The Paste Special command is usually available in the right-click shortcut menu or the Edit menu or the Paste control on the Home tab for newer Excel versions.

Finding the Numbers in a worksheet

When you have numbers in a worksheet that you need to select and you don’t want to have to do the selection yourself try using Go To Special.

For example, in the previous example I wanted to select the numbers on a worksheet so that I could multiply them. I did not want to select the cells containing formulas, just the cells with normal numbers or, in Excel speak, the numeric constants.

Choose Go To Special and then click the controls to specify what class of worksheet data you want to have selected. In this case, the Constants option button and the Numbers check box. Click the OK button and Excel selects those values wherever they are on the active worksheet.

Should you select a range before choosing Go To Special then the cell selection is confined to the currently selected range.

So, where is Go To Special? It depends on the version of Excel that you are using, if you have one of the older versions with the drop down menus then you should choose Edit, GoTo and then click the Special command button. In newer Excel versions with the ribbon, go to the Find & Select control on the extreme right hand side of the Home tab, click the control and it’s in the drop down menu.

Related Courses

Microsoft Excel Introduction – Link

Microsoft Excel Intermediate – Link

Microsoft Excel Advanced Course

Excel Advanced Course

Macros

Pivot Tables

Stand Out. Get Certified!

Related Posts

Excel Essential Functions Course

Essential Excel Functions

Filter Functions

Logical Functions

Lookup Functions

Related Posts

Microsoft Power BI Courses

Microsoft Power BI Courses

Power BI Introduction

Power BI Advanced

Mastering DAX

Related Posts

Microsoft Excel Pivot Tables Course

Excel Pivot Tables Course

Create Great Pivot Table Reports

Related Posts

Keep your Microsoft Excel Skills Up to Date

Update Your Excel Skills

Excel 365 New Functions

Excel 365 Power Query

Related Posts

Mouse Training and Prodigy Learning- a Success Story

Microsoft Office Specialist Certification

Benefits of MOS Certification to Test Centre Candidates

Solving the lack of Digital Skills for Employers

Certification to Secure Senior Position in Leading Supermarket Chain

Digital Badging to Prove Skills to Employers Globally

Earning Microsoft Certification at Home during Global Pandemic

About Prodigy Learning

Word Doing it with Styles

Table of Contents

Word Styles

Applying Styles

Heading Styles

Draft View

Set the width of the Style Area Pane

Modify your Styles

Changing the Normal Style

Changing the Heading Styles

Benefits of using Styles

Saving your Styles

Create a Style Set

Cleaning Up Existing Documents

Using your Styles

Navigation Pane

Using the Navigation Pane

Table of Contents

Hyperlinks and Cross-references

Creating a Hyperlink

Creating a Cross-reference

Updating your References

Paragraph Numbering

Using a List Style

Outline View

Creating a PDF file

Doing it with Styles Conclusion

Related Courses

Excel Converting USA Dates to UK Dates

Table of Contents

Converting USA Dates to UK Dates

Country Setting for Dates

Reversing the Date Evaluation

Converting UK Dates to USA Dates

Making the Date Converter macro

Step 1. Recording a macro

Step 2. Using the Personal Macro Workbook

Step 3. Finding the recorded macro

Step 4. Copying and Pasting the code for the macro

Step 5. Saving the macro

Step 6. Creating a shortcut for the macro

Step 7. Customizing the Quick Access Toolbar

Step 8. Assigning the macro to the QAT

Step 9. Modify the macro button

Step 10. Test the macro