Microsoft Excel Training | Online Excel Training

How Online Excel Training Courses Can Become Your Most Powerful Ally In The Office

Excel Training admin
Being proficient in Microsoft Excel in today’s corporate environment isn’t an option – it is, in fact, a deal breaker if you confess to not knowing and loving the friendly green monster. If you wok in an office environment like 72% of the US population, you will be asked (if not, already) to use Excel to manage your financials on a regular basis and if you’re not equipped to handle this task, then it might be time to enroll in an Online Excel Training Course. When was the last time you opened a computer at work that did not have Microsoft Excel installed on it? It just is, and there’s no getting around this, really, the default spreadsheet software of the corporate world. And when in France, you just gotta speak French!

And it’s not just the whiz kids down in finance and accounting who have to be conversant with Microsoft Excel, either. Doesn’t really matter which department it is, in fact, you have got to know your rows, columns and function to be able to, well, function. If writing is in Word and pitching is in PowerPoint, then number crunching is in Excel. Although you’d be barking up the wrong tree if you thought Excel was just about the numbers. Charts, spark lines, heat maps, SmartArt are all relatively non-numeric things that Excel is rather good at as well. And we haven’t even started with all the hidden goodies that Excel keeps in store. The Developer ribbon, for example – that relatively little known store of Excel goodness. All of these things are applicable in all departments, all over the company.

Take human resources, for example. Want a neat little database of who’s working in your company, and where they were previously, and what their phone number is, and who they report to, and on and on and on? You’re thinking, at the very least, a nice little pivot table in Excel, and knowing database functions wouldn’t hurt either. Plus, no matter which department you’re in, you will, sooner or later, raise an invoice. Use one of those nice pre-formatted ones that Excel makes available – and if you don’t know what I’m talking about, well, that’s what online courses are for! Computing offer sheets, calculating severance pay, figuring out how to divvy out bonus pools across departments – ask Excel, and it shall deliver.

What about marketing, you might think by way of response. Surely those guys don’t have to be familiar with Microsoft Excel? Au contraire, my friend. Keeping a list of prospective, current and past clients handy, and figuring out how your billing has changed over time, and linking customer tables with products purchased will require intimate familiarity with the magic spell that is VLOOKUP,

Excel Everest Collaborates With Acuity Training On A Strategic Partnership

News admin

Excel Everest is pleased to announce a strategic partnership with Acuity Training to offer high quality Microsoft Excel training to clients of Exceleverest.com who are looking for classroom-based training in the UK.

While online Excel training works well for many people it isn’t always the correct solution for everyone. Some people prefer classroom-based learning and so to meet this need Excel Everest has partnered up with Acuity Training in the UK.

Acuity Training is a specialist IT training business exclusively focused on classroom-based IT training courses. Acuity is one of the UK’s leading Excel training providers, with training clients including Exxon, GE, Lockheed Martin, Deutsche Bank, Johnson & Johnson and Novartis. It consistently gets outstanding feedback from its delegates, which is publishes publicly here. It offers scheduled Microsoft excel courses in London and Guildford as well as onsite training . This link gives full details of Acuity Training’s scheduled excel courses.

Ben Richardson, CEO of Acuity Training “I’m delighted to announce this partnership. Excel Everest’s offering is fantastic but doesn’t work well for everyone, just as classroom-based training doesn’t work well for everyone. We don’t all learn best in the same way. This partnership allows us to work with a similarly quality-focused Excel training organization to offer our clients outstanding training results through whichever form of training will work best for them as an individual.”

Preserving Leading Zeros

Resources,Tips and tricks admin

The always awesome Chandoo comes up with a tip that needs to shared as widely as possible, more about which in just a second.

Have you ever needed to enter numbers in a sheet where for whatever reason, they need to be entered as 001, rather than 1?

Of course you have, who hasn’t? Well, in that case, you have also obviously suffered from the bang-my-head-against-the-wall syndrome, because Excel is, in this case, more stubborn than an obdurate mule.

If you don’t know what I’m talking about, simply fire up an Excel sheet and enter “001″ without the inverted commas in any cell

… and hit Enter

Slightly frustrating, isn’t it? You want Excel to show the leading zeros, but Excel couldn’t care less.

There are a couple of  workarounds to this, which are mentioned in Chandoo’s blog post linked to above, but there’s one that is even simpler than the solutions he has mentioned… simply append a leading ‘ to the number you wish to enter.

Once you press enter, the leading zero’s don’t disappear!

The only thing is that Excel gently reminds you that what you’ve entered as text really seems to be a number:

And the number, since it has been formatted as text, is left-aligned. But it is a pretty quick workaround all the same!

The Top 5 Keyboard Commands You Really Should Know

Functions,Resources,Tips and tricks admin
Well, I’ll be honest here. There are more than five. In fact, there are probably fifty. On the other hand, five are easy to remember, and the title is a lot more catchy. In all seriousness though, what we are talking about in this Excel tutorial are commands that are inordinately useful and for the most part, little known. But get to know them hear, master them, and watch productivity zoom ever upwards. The SPACEBAR Series CTRL+SPACEBAR is a particularly gratifying shortcut to get acquainted with, since it selects the entire row in which a cell happens to be active. Particularly useful for copying for large swathes of data from one column to another. ALT+SPACEBAR does exactly the same thing when it comes to rows, while CTRL+ALT+SPACEBAR does exactly what you’d expect it to – go ahead and give it a try! The SPACEBAR series works like a charm for large amounts of data, when you’d rather not take the trouble of scrolling to the end of the data in order to select it entirely. SHIFT+END+DOWN Which brings us rather conveniently to the next shortcut. If you want to select a range of data that (it would seem) extends down for many rows altogether, you want to select the first cell in the data, like so: …and simply press SHIFT+END+DOWN (the down arrow, that is). You’re through! The entire data is selected. This works in all directions, so if you are at the start of a particularly large and cumbersome array, pressing SHIFT+END+RIGHT, followed by SHIFT+END+DOWN will select the entire array. The only thing you need to keep in mind here is that this works assuming there are no missing rows/columns in your data. So long as you keep that in mind, you’ll find this is a pretty useful trick to know. ALT+SHIFT+RIGHT The “Group” feature in Excel has been a boon for many an intrepid spreadsheet traveler. It helps you literally group two or more columns/rows together – something that comes in particularly useful for large complicated models. Well, the ALT+SHIFT+RIGHT command helps you group rows/columns together in a jiffy. ALT+SHIFT+LEFT ungroups them. Simple, eh? CTRL+SHIFT+’ This one’s not all that well known, and getting used to it is a useful, pleasant surprise. All of us have faced situations in which we need to enter the same value in a cell as is present in the one above. For example, let’s say cell B3 has the value 4. Well, B4 should also have exactly the same value. Enter CTRL+SHIFT+’. It does exactly what is required, no more, and certainly no less. (By the way, you might want to give CTRL+SHIFT+>, and CTRL+SHIFT+< a try as well – they can copy formulas over from the left and th

Top 5 Excel Formulas That Are Just Plain Weird

Formulas,Tips and tricks admin

You might think this post to be a little offbeat, a little off the beaten path – and we wouldn’t argue with you.

What we have done in this Microsoft Excel tutorial is looked through Excel’s capacious archives to come up with five formulas that won’t be used all that often. Sure, there’ll be people over the world who use these day in and day out, and thank heavens for the very existence of these formulas – but there won’t be mroe than ten of them, I’m sure.

All right, that’s an exaggeration, maybe – but seriously: how many of you knew about the “Roman” formula, for example?

a) The Roman:

The Roman formula does what the Roman formula promises. It returns the Roman value of an Arabic integer. Arabic integers are the good old numbers that we always end up using. 543, for example. But I bet you a rather sizeable sum you can’t come up with the Roman numeral equivalent. Well, Excel can. 

It’s DXLIII, for what it’s worth. It may be a neat party trick, or it may be of great help if you are in academics. But it’s a useful thing to know, the Roman formula – that’s for sure.

 

b) Combin

In how many ways can you choose 2 objects out of, say, 50? Brings back horrific memories of math in the sixth grade or thereabouts? Are you about to break out into hives at the mere thought of that accursed blackboard and those dreaded sums?

Fret not, want not. Fire up Microsoft Excel, and enter “=combin(50,2)” in any cell and hit enter. Learn that there are 1225(!) ways of choosing 2 objects out of 50. Who would have thought?

c) Now

We couldn’t resist putting this one in for it’s immense, well, coolness. Any formula called “Now” deserves to get in simply because it is called “Now”. Still, it does what you think it would. It simply returns today’s date and time as of… wait for it… now.

d) Rept

This is Bart Simpson’s dream come true. If only the blacboard was a spreadsheet!

The rept formula takes a text string as an input, and spews it out as many times as you like.

Take that…

Use this…

A Treasure Trove. Literally.

Resources,Tips and tricks admin

Every now and then, our sojours on the Internet seas lead us to places where not too many people have trodden before.

These virgin, unexplored islands are often that-a-ways for a reason; they’re poorly designed, perhaps, or maybe the content is not too useful. We just heave resigned sighs and set sail from them, in search of better lands.

Every now and then, though, we just luck out, plain and simple. And the link that we’re going to share with you today is one such internet treasure.

The unimaginatively titled “Site Map | Microsoft Office” – I mean, c’mon! – is actually a repository of link related to Microsoft Office products. And when I say repository, I mean every single link one could ever hope to keep in one’s bookmarks and when I say Microsoft Office products, I mean every single last one of ‘em.

Just insaney good stuff, this.

Just scroll down from the rather drab SmartArt-ish graphic at the top of the page…

… and immerse yourself in the Excel links section.

We’ve previously talked about Excel’s own help section, and it is pretty darn good. But if ever you need to forage through the big bad world wide web… well, this is as good a place as any to start.

Happy surfing!

Stop the presses! There’s a game based on MS Office

Add-ons,Resources admin

Microsoft, meet fun.

Fun, this is Microsoft.

It’s not often that you get to say something like this, which is why we’re going to milk it for all it’s worth.

Ribbon Hero 2 is a game based around the ribbon layout in MS Excel and other MS Office program. Clippy (remember him?) is resurrected from the dead, and stars as the unlikeliest of heroes in a game that also acts as a tutorial.

It’s fairly simple to play, and while the first few steps are simplicity itself, it should get fairly interesting at the advanced levels – or so we hope.

In any case, give it a whirl, and let us know what you think!

Page Layouts: Confusion Reigns Supreme!

Resources,Tips and tricks admin

While we are unabashed fans of Microsoft Excel here at Excel Everest, there are some things that can drive us up the wall.

And the leader on the Black Sheep List undoubtedly has to be that much-maligned, much-misunderstood feature: Page Layout. If you broke out into hives upon reading that dreaded name and are seriously contemplating diving under the desk until the storm blows over – hey, we know exactly how you feel!

This blog post is actually a dipping of the toes into the water, if you will; we just want to take a quick poll about how many of you think a deeper post about Page Layouts is warranted. If there are enough of you, and we see no reason why not, we’ll return with an in-depth tutorial.It is also is a bit of a rant – you’ve been warned!

First things first: Microsoft Excel does itself no favors by having a tab called “Page Layout”…

… and a button called “Page Layout” in the tab View

Now, that’s just bad design. Trust us when we say that using Page Layout is confusing – a tab and a button with the same name is just cruel!

Well, anyhow, when we say Page Layout, we mean the button in the tab “View” – unless specified otherwise.

There, now that we’re clear about that – here’s what Page Layout does:

It helps you visualize how a printout will look. In other words, if you were to hit the print button, what would the printer make of your current spreadsheet? Well, whatever you see on the screen after hitting the Lage Layout button, that’s what.

As you can see in the screenshot above, Excel allows you to configure your layout as per your requirements. You can adjust headers and footers, the lage layout itself, decide how much content you need per page and so on and so forth. Think of it as the advanced version of “Print Preview” – which in fact is what this is.

But in our honest opinion, Microsoft bungled up this bit. In fact, Microsoft itself has, in a way, admitted as much. They have  a blog up asking us users of Excel about how many of us use Page Layout, and in what way. Feel free to head on there and fill up the survey – it can only help.

As you can see, when I filled in the survey, the results indicated that most people did not use it at all, or used it very sparingly inde

Need an Excel Template? Vertex42 Probably Has it.

Add-ons,Resources,Tips and tricks admin

A fundamental reason we built Excel Everest is that Excel is useful in many facets of life. Recently we’ve been playing around with some templates from Vertex42.com, a site that embodies this to the core, and they’re spectacular. Check them out if you’re looking for an easy way to plan your mortgage, put together a wedding budget, build a contact list, etc. 

Below is our editor’s account of the kick-ass meal planner provided by Vertex42!

I’m going to describe a recurring nightmare that I have at least three times a week…

It involves me standing in the middle of the kitchen, with a furrowed brow and with some gentle headscratching involved. There is but one question running through my head on endless loop, and no answer forthcoming for the longest time ever. The question being: What do I make for dinner? And here’s the thing: this nightmare is for real! I live it! Every week!

I don’t know about you, but planning meals for my home gets my goat like nothing else on earth. It’s tedious, it’s boring and it’s frustrating. It is also a known and acknowledged imagination-killer.

Imagine my delight, therefore, when I stumbled upon an easy, permanent solution to my problem. And knowing as you do by now my eternal love for all things Excel, imagine how my delight doubled when I realized that the solution came in a neat little Excel package that was… wait for it… free! You see what I mean when I said the Excel Tutorial this time around was manna from heaven?

Well, to cut a long story short, Vertex42 is a site that specializes in Excel templates – of which they have many more than we can count in one blog. You should head over to the site, pronto, and give it an approving once-over. Go on, give it a look – we’ll wait.

Impressive, isn’t it? Exactly the same reaction I had when I first saw it. But as I was saying, the thing that made me happiest was the Weekly Meal Planning Template. The template allows you to download an Excel file (although PDF versions for simple printouts are also available) which you can use to plan your meals.

Here’s what the file looks like:

Enter the date in the relevant cell, and head on over to breakfast. Now this is where things get really interesting:

Because the cell next to breakfast has a drop down button that contains a list that is guaranteed to get your tummy rumbling. Pre-fed into that cell are various options for breakfast – and pre-fed into the cell adjacent to it are options for accompaniments for breakfast. And it’s the same level of convenience i

Visualizing Data (And it’s easier said than done!)

Resources,Tips and tricks admin

Excel in its latest and greatest avatar has significantly improved upon the ability to create charts that:

  1. Look respectable (think of the truly horrible grey background that charts had in Excel 2003 and earlier)
  2. Convey meaningful information (the ability to creat new kinds of charts has been added)
  3. Can be interpreted easily (with color schemes and chart combinations that make sense!)

Still, given your data, creating a chart that is easy to read, conveys meaningful information and communicates a relevant message can be difficult. But hey, what are we here for? To make your Excel life as easy as possible, that’s what for! This particular Excel tutorial focusses on getting out that perfect chart – read on to find out more!

Take a look at the data given below:

Now, if you look at that table long enough, some things make themselves apparent. Jim, it would seem, is sheer awesomeness – highest sales by far. But hey, hang on a second, he’s not very efficient, is he? In fact, he needs to make a lot of calls, or sales visits, to get a succesful conversion.

And Peter – lowest sales on record, but what a salesman! He’s able to convert 95 times out of 100, and who wouldn’t want a batting average like that? Now, our problem is, can we put up a chart that tells this story effectively?

Well, creating a chart is simple enough – choose your data range, head on over to the Insert tab, and click a chart that catches your fancy – a bar chart would work well in this case.

Choosing the very first chart (first under 2D Columns) will give a chart that looks rather like this:

Which is well enough as things go, but there are a couple of problems. The first, and most obvious problem, is the fact that “% Successful Calls” is on the same vertical axis as “Sales”. Since sales are around the 200 mark (or thereabouts), and percentages are necessarily capped at 100%, well, “% Succesful Calls” variation doesn’t come across too well.

There’s a workaround though: right-click on the red bars, choose “Format Data Series”, and opt to have these bars shown on the “Secondary Axis”

Umm, whoops. That actually creates a secondary problem, as you can see in the chart below:

Top 5 Excel Keyboard Shortcuts

Equations,Resources,Tips and tricks admin

Excel Keyboard Shortcuts – My Top 5 List

There is, I assure you, a very high degree of correlation between amount of time you spend on Excel, and the number of Excel keyboard shortcuts you know. Over time, it becomes especially aggravating to move your hands from the keyboard and on to your mouse in order to use some feature or the other in Excel. It might seem like a trifling thing to begin with, but those seemingly inconsequential seconds can add up to become entire eras in their own right.

Which is why keyboard shortcuts are a blessing from above, and which is why you should know as many as possible. I’ll let you in on a little secret though: even power users have their own personal favorites that they just can’t do without! And in this tutorial on MS Excel, I am going to talk about my personal top five keyboard shortcuts. They aren’t rocket science, each of these five, but if you integrate them into your routine while working on Excel, you’ll soon find yourself saving a lot of time.

  1. Alt+E+S: The Paste Special is a tool that has saved many an Excel user from collapsing in a heap on the psychiatrist’s couch, and with good reason. It allows you to copy cells over with special tips and tricks, such as pasting cells and retaining only the values, or only the formats, – or what-have-you. You can even paste the data after transposing it, that is, you can copy a row of data, and paste it as a column. But invoking the magic in Excel can be done by either clicking on the little black triangle under Paste on the Home ribbon…

or by hitting Alt+E+S. And once you have practiced it a bit, you’ll see that the keyboard shortcut works a lot better and a lot faster. Go ahead, practice it for a while, we’ll wait.


2. CTRL+Pg Up and CTRL+Pg Dn: This one took me a while to get used to actually, and I’ll admit, it was immensely frustrating until I did make it a habit. Windows users have been indoctrinated thoroughly into using Alt + Tab when they want to switch windows – and the natural tendency of Excel users is to hit that same combination of keys if they want to switch sheets in an Excel file. Except, of course, it doesn’t quite work that way. To switch sheets, you need to hit CTRL+ Pg Up or CTRL + Pg Dn. Keep this Excel 2010 keyboard shortcut in mind to save yourself the trouble of going “Arrrgghh!” every few minutes or so. I speak from personal experience!
 

3. Alt+W+V+G: Spreadsheets sans gridlines look much better than, well spreadsheets non-sans gridlin

1 2 3 4 7