Welcome to My Online Training Hub.

**We know that skills in Excel, Word and Outlook give you more job opportunities. And having advanced skills makes you indispensible. **

**That's why we make it easy to get your skills up to date fast.**

In 2003 Phil and I left our corporate lives in London behind for the sunny climate of Australia.

Initially we worked with many different businesses and on a daily basis we'd get asked how to do things in Excel, Word and Outlook that for us were 'must know' skills. It was an eye opener.

We soon realised that a lot of people were lacking some of the basic skills they required to do their job effectively and efficiently.

It's no surprise to us, as we know classroom based training is expensive and it's a major inconvenience for most businesses to lose an employee for a day of training.

We enjoy sharing what we know and weβd like to help you too, because once you get to grips with a few of the basics youβre going to take off. Youβre going to see how much easier it is, how much more time you have and how much less stress you have.

Our Microsoft Office training is not only affordable, almost 1/3 of it is free. The free training is the minimum we think you should know, and if you like what you get for free there's a paid Premium membership option available that will fast track your training so you can get your skills up to date quickly.

In addition to the free Microsoft Office video training, there's loads of tutorials on our blog.

We don't profess to know everything, so if you have a better/alternative way to do something please share it in the comments for everyone to benefit.

## Get in Touch

We'd love to keep in touch.

Why not sign up for our free training and try it out, or if you're an Excel fan subscribe to our Excel Newsletter and receive our 100 'Must Know' Excel Tips and Tricks e-Book free.

Or if you just want to say 'Hi', or give us some feedback, we welcome it, the good and the bad and the ugly. That's how we learn to make this site better.

## How You Can Support Us

The best way you can support us is by spreading the word.

Please share our site with your friends on Facebook and Linkedin. We'll love you forever for it π

Sincerely,

Mynda and Phil Treacy.

Tom Lannom

Looking for some advice. I need to replace an old database program. Started by making a spreadsheet with one tab with all of the source information. The tab has active employees with up to 40 columns of information. When someone leaves the employee infoamtion is moved to an “inactive” tab. To create reports similar to what was generated by the other database program, I took tabs within the same spreadsheet and linked certain information using if statements. Problem is users are having to select first line of each tab and pull down to refresh because employees are removed from the active and moved to the inactive tab. Speed is another problem because doc the size of the file. There are a few other challenges. Got me thinking do I need to look at a database program like Access. I know you are a big fan fan of excel but I wanted your input. I did start taking the dashboard courses but did not finish. Wondered on what you would suggest.

Thanks in advance.

Tom Lannom

Catalin Bombea

Hi Tom,

How many records (rows) are in your sheets? it will be a lot more relevant to see how data is organized in your file and your formulas, from the info you provided you may need to improve the data structure and formulas. If you have a very large database, with more than 50.000-100.000 records, Access is an alternative, otherwise you can use excel.

Cheers,

Catalin

TomLannom

The source tab has 100 to 200 lines per tab. I will email you a sample of the file. Thanks.

Catalin Bombea

Great, i’ll take a look at the file.

Catalin

Cam Lowe

Mynda and Phil, Apologies – I’m a member and subscriber, but was wondering about multiple memberships for my colleagues at work – many of who are beginners, but use Excel and Word alot; how do I best inquire about a membership for a group of say, 5? or should be simply just sign up individually.

Many thanks and I enjoy immensely all the help I’ve gotten from you.

Cam

Mynda Treacy

Hi Cam,

Thanks for your kind words.

You can either sign them up one at a time, or I can create a single payment link and we can set up the members for you. I’ll email you to make arrangements.

Kind regards,

Mynda

Adrian Ho

Hi, Great site, I have really improved my excel skills thanks to your tutorials. I am stumped on a particular challenge though. I want to model payroll taxes by location that incorporates income limits. (by each location) An IF function would be way too long and complex to do in my opinion as I have 13 locations to model. Is there an excel function that will allow me to chose a formula dependent on a location?

Mynda Treacy

Hi Adrian,

Thanks for your kind words. It’s great to know we’ve helped.

If you have more than 3 or 4 conditions then I prefer to use a VLOOKUP to a table with the conditions as described here:

https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-formulas-explained

Kind regards,

Mynda

Tom Lannom

I have a tab containing several columns of information and I am trying to pull the information to other tabs (or reports). I have been using a simple =IF(Active!$A2=0,””,Active!A2) formula to pull information but when lines on the Active tab are deleted it gives me a #REF error because the row has been deleted. So I looked for an alternative because I want the information to automatically refresh and not have to re copy the information down on the report tabs. Some one suggested using =IF(INDIRECT(“Active!A”& ROWS($1:2))=0,””,EmployeeNumber). It worked except for the autofilters on the columns on the report tabs do not work now. I can’t sort dates from early to latest. Ideally I would like each report tab to automatically refresh and be able to be sorted by autofilters for the columns. Any suggestions. Thanks.

Catalin Bombea

Hi Tom,

Can you upload a sample of your file, to analyze it? Use our Help Desk

Cheers,

Catalin

Dan Sheets

You guys are great. This site has excellent information and tutorials. Thank you for all of the excellent articles, tips, lessons, and more.

Mynda Treacy

Thanks, Dan! We’re glad you found it useful π

guman

Nice website for excel Learning

Mynda Treacy

Thanks, Guman π

Thant

Hi Mynda,

I can’t use “Average function” more than 30 values? Why? Is there any other way to use such “Average function” for more than 30 values?

For example =AVERAGE(VALUE1, VALUE2, VALUE3, . . ., VALUE30)

Regards,

Thant

Mynda Treacy

Hi Thant,

You can AVERAGE many more than 30 values by giving it a range of cells like this:

=AVERAGE(A1:A1000000)

But AVERAGE like this is limited to 255:

=AVERAGE(A1,A2,A3,A4…..,A255)

Kind regards,

Mynda

Christopher

Hello!

I’m in the middle of doing a summer internship with a company that engineers and manufactures gas and oil pumps and compressors. I was just given a project where they want me to create and implement an operations metrics dashboard that is user friendly for each of the managers from each cell (4 Cells) to update every month. Without any guidance I feel like it would turn out very amateur /:

Mynda Treacy

Hi Christopher,

That sounds like an exciting project to work on. If you haven’t got any dashboard experience it might be a bit daunting though.

I have an Excel Dashboard course which will teach you everything you need to know. You can find out more here.

Please let me know if you have any other questions I can help with.

Kind regards,

Mynda

Sharon

I am using Excel 2000, will I be able to create dashboards?

Mynda Treacy

Hi Sharon,

Excel 2000 is very old. You won’t recognise the Excel that I teach in my videos as it looks very different to Excel 2000. Are you certain it’s Excel 2000? I’m just wondering if it’s a typo.

You can double check by going to the Help menu > About.

My Dashboard course is suitable for anyone using Excel 2007 or later.

Kind regards,

Mynda

Sam

I like to learn Xcel

Mynda Treacy

You’ve come to the right place, Sam π

Shari

In excel if you want one column to have a running total to another column, how do I make it work? Ex: C2 + B3 = C3; C3 +B4 = C4 and so on. Also, do I have to do each column individually or is there a way to do the whole rows at the same time?

Catalin Bombea

Hi Shari,

in cell C3, you can place the formula: =C2+B3

All you have to do is to place the cursor to the bottom-right corner of the cell C3 , and drag down the fill handle to all your range, the formula will be adjusted autamatically

Catalin

Narendra

I want to become awosome in excel and want to do my own training classes. so how to start?

pls suggest.

Mynda Treacy

Hi Narendra,

One of the best ways to improve your Excel skills to to answer questions in forums. It gives you new challenges that are often more difficult than average, plus you’re helping people which is rewarding and similar to training.

Kind regards,

Mynda.

samson

Hi Mynda,

Can one bypass the normal excel training to learn dashboards straight without struggling to catch up ? considering the person is just an average user.

Also, are siignments/tasks given for each stage of training for the regular excel classes?

Mynda Treacy

Hi Samson,

Without knowing your current Excel skills it’s tricky to know if you can ‘bypass the normal excel training to learn dashboards straight without struggling to catch up’. It all depends what you already know and how quickly you learn.

I recommend you look at the syllabus https://www.myonlinetraininghub.com/dashboard-syllabus-sales. You’ll see the second column lists the Related Excel lessons. These are things you need to know for the dashboard course. I’ve included them because not everyone is at the same level when they start, so if you don’t know a topic the training is there to help you get up to speed.

I would say that if you already have Excel experience then you will be able to do the course.

There is one (optional) homework assignment at the end of the course which I will review and give you feedback on if you choose to complete it. All of the Excel files used in the filming of the videos are available to download so that you can practice what I’m teaching using exactly the same data. All instructions are in the Excel files too so you can use them independently of the videos.

I hope that helps. Please let me know if I can answer any other questions you might have.

Kind regards,

Mynda.

Vinyas Shreedhar

Hi Mynda….

Though I am not a paid member of this site, I am getting addicted to your website as I have lots to learn from your site. I always keep it open for referring excel formulas and tips. Though I am not able to take the Excel tutorials due to financial constraints, I am sure I am gonna save some money one day and sign up for your course. Just wanted to express my thoughts……

Vinyas Shreedhar (from India)

Mynda Treacy

Thanks, Vinyas. It’s rewarding to know we can help with our free tutorials and blog.

Kind regards,

Mynda.

Peter

Hi Mynda,

I just take sometime to read the free training materials you send to me from two months ago. They are very useful and helpful to improve the Excel skill. I will keep reading them when I have time. That is the best site for Excel training.

Thanks.

Peter

Mynda Treacy

Thanks, Peter π It’s great to know we could help.

Kind regards,

Mynda.

Frank

Hi

How can we sum up all visible cells using macro?? There has to be shortcut using macro to sum up visible cells??

Mynda Treacy

Hi Frank,

I would use the SUBTOTAL function to do this. VBA is way more complicated and not necessary when you see how easy the SUBTOTAL function is.

Kind regards,

Mynda.

Hamid

I have just heard about your web page when i saw one of my friend using your web page just asked him to give me some information about and really I surprised and now a want to register to your daily training system.

Thank you too much.

Hamid

Mynda Treacy

You’re welcome, Hamid π

Please let me know if I can answer any questions you might have about our training programs.

Kind regards,

Mynda.

Neguiel Francis

Mynda – I have a shared portal that is use to share all MicroSoft file types with my partner hospitals. The partners are unable to see the Excel dashboard file but my internal colleague can see and open this file just fine. Could there be something important I am forgetting to do so the partners can see the dashbpoard?

Thanks,

Neguiel

Mynda Treacy

Hi Neguiel,

I guess the first thing I’d check is that the partners have Excel installed on their PC’s. If not they won’t be able to open it.

Kind regards,

Mynda.

Jain Nitesh

Hi,

I have a workbook, which when I open it gives me a corruption error:

“Excel found unreadable content in ‘Register.xlsx’. Do you want to recover

the contents of this workbook? If you trust the source of this workbook,

click yes.”

Repaired Records: Table from /xl/tables/table8.xml part (Table)

Can u help me out on Urgent basis

Regards,

Nitesh jain

Mynda Treacy

Hi Nitesh,

I’m sorry I can’t help you with this. I suggest you do a Google search to see if you can find a solution.

Kind regards,

Mynda.

Alan T

Hello Mynda ,Carlo

Please assist me to create a formula for my team.Condition is given below :

I have two group of people say “A” & “B” .Now if group “A” achieve sales target between 10-20 will get 10 points & if group “A” betwn 21-40 will get 12 point.Similarly if group “B”achieve target between 10-20 will get 13 points & if group “B” achieve betwn 21-40 will get 14 points and also if Group “B” Achieve betwn 41-50 will get 16 points .

Thanks in Advance

Mynda Treacy

Hi Alan,

I would use a VLOOKUP formula for this. You can see an example of it here.

Please let me know if you get stuck.

Kind regards,

Mynda.

James

Thank you!

Your tutorial on how to use vlookups was really helpful. I have some Excel testing tomorrow, the results of which might determine whether or not I get a better job, so it was important that I get a greater understanding of some of Excel’s finer points. You’ve really helped. Thanks again!

James

Mynda Treacy

You’re welcome, James. Hope the test went well π

Kind regards,

Mynda.

Alan T

Hi Mynda & Phil,

I need your help to add this two formula plz ignore the previous one

1)

=IF(D2=”A”,IF(AND(C2>=10,C2″B”,IF(AND(C2>=10,C2=21,C2″B”,IF(AND(C2>=21,C2<=40),8))))

Thanks in advance

Alan T

Hi ,

Please help me to add this two formula below

=IF(D2=”A”,IF(AND(E2>=10,E2=10,E2=21,E2=21,E2<=40),13))))

Thanks in Advance

Carlo Estopia

Hi Alan,

I see that there’s nothing wrong with this one, except that you did not specify the false value

of your last IF statement.

So my advice is that please send your file and what you want to do with it here: HELP DESK.

Please try to read more on IF SYNTAX.

Cheers,

CarloE

Alan T

Hi Mynda,

That was really awesome thanks a lot Mynda .Here i can realise the power of VLOOKUP in calculation .

Thank You

Mynda Treacy

Thank you, Alan π

Carlo Estopia

Hi Alan,

I don’t exactly know what logic are you trying to get here but I

just made this formula work without minding the result:

Cheers,

CarloE

Alan T

Hi Mynda/Phil,

I am facing a problem in solving a issue with if and formula.Its Like this

I have two group of People Group A & Group B now if Group A score between 10 to 20 then Group will get 10 point where as if Group B scores between 10 to 20 will get 11 point.

Group Name Score Point

A 10-20 10

B 10-20 11

Kindly assist me to resolve this calculation

Carlo Estopia

Hi Allan,

Try this formula:

Read more on Nested IFs

Cheers,

CarloE

Alan T

Thanks a lot Carlo

Carlo Estopia

Cheers Alan!

Ashish

I have data in excel need to create a good visualization dashboard. Can anybody help me in making me the excel dashboard?

Mynda Treacy

Hi Ashish,

I’m sorry we only provide training in Excel. You might be interested in our Excel Dashboard course though.

Please let me know if you have any questions.

Kind regards,

Mynda.

shabani kassim

hi! things I want to know computers in greater detail. I will appreciate if you help me

Carlo Estopia

Hi Shabani,

Computers is somewhat broad for a term.

What specifically about computers you want to learn?

We offer Excel, Word, Outlook and other MS Office Support.

Cheers.

CarloE

Dayanandam Yogendran

Dear Sir / Madam,

Please be good enough to forward me new update excel formulas.

Thanks

Yoga

Carlo Estopia

Hi Dayanandam,

You may start with this link:

Assorted Tips and Tricks

You may type in your desired topic in the search box.

Cheers.

CarloE

Chris Rule

Hi I own and run a financial planning firm and have developed a “debt Reduction” strategy upon which the business has run for about 10 years. Its in excell but I have no training so its clumsy, has no macro’s (because I dont know how to use them), formulars are basic. I have the opportunity to take the process I use nationally and the software needs to be set to allow other planners to use it. I need to be able to ask “how to” questions as they arise as I dont have the time to do formal training. Can you help and what is the fee structure if you can?

Mynda Treacy

Hi Chris,

I’d love to have the time to help you but unfortunately I don’t. One option is for you to hire someone to develop the model for you. You can get very affordable contractors through websites like oDesk.com or Elance.com.

I have used contractors through these websites with good results. All the best.

Kind regards,

Mynda.

Dhananjay Pansare

THANK YOU SO MUCH FOR HELPING O US

Dhananjay

Mynda Treacy

You’re welcome, Dhananjay π

ahtasham bahadur

Dear sir,

I am too much happy about your free online training course and it provide better result to learning person for improving skills in your course . Sir one thing clear to me first i am living in pakistan and needy person but your premium video is not availble how we prepare lectures of premium videos and not money in my hand so that i can purchase premium membership . I hope you will give me complete feed as soon as possible.

yours sincerely,

Ian

Hi,

Can your dashboard coarse be taken up at any point or is it time dependant? If it is time dependant, when can people access it again?

Many thanks,

Mynda Treacy

Hi Ian,

The dashboard course is released a few times each year. The reason is because I provide an additional 6 weeks online support for the students and I can therefore only release the course when I am able to commit to the support.

The dashboard course will be open again in January 2013. I see that you are on our email list to be notified when it’s open, so keep your eye out as there will be an early bird discount available if you join in the first week of registration.

Kind regards,

Mynda.

jim morley

Hi Mynda,

I have been following your site and tips for some time now and regularly recommend your services to everyone I know as your site is fantastic and simply the best excel resource I have found anywhere on the net. I am not what you would call an advanced excel user but I have mastered the majority of the basic tasks. However I have one question that I have not been able to answer.

I need to update monthly a master worksheet in a workbook with data from various other worksheets within the same workbook. However I never know how much data will be within each spreadsheet (i.e. how many rows in each worksheet) so how do I manage to copy the data from each worksheet into the master sheet which vary dynamically ?

I thought this might be a good topic for one of your blogs and your assistance to me would be greatly appreciated.

regards,

Jim Morley

Mynda Treacy

Hi Jim,

Thank you for your kind words. It’s rewarding to hear we are helping π

Now, to answer your question. It sounds to me like your process is back-to-front. Ideally you would have one master worksheet which is where the

alldata is entered. We’ll call this your database. This would be in a tabular format. That means there are column headings or labels, but no row labels, and preferably no blank columns within your table.You then use PivotTables or formulas to extract/summarize the data for the ‘other worksheets’. You could also have a ‘master summary’ that also references your database.

Formatting your data in one ‘database’ and then analysing it is good spreadsheet design. The way you are recording your data in separate sheets that you then have to consolidate is causing you extra work.

If changing your workbook as I suggest above isn’t an option, then the only way to automate copying and pasting data is with VBA/macro.

I know that changing your process may not be very appealing but it will be well worth your time and effort in the long run. I hope that helps.

Kind regards,

Mynda.

James Patric

I am developing an excel sheet for my physical education classes to keep track of the fitness scores and I ran into trouble. I have mile times for the fall and spring. I want to subtract the spring time from the fall time to get a difference in time. I used your example of subtracting time zones but it subtracts to the prior day. Is there any way to get a negative time that shows minutes……for example fall time of 6:15 – spring time of 7:15 = 1:00 instead of 23:59:00?? Let me know if you have an answer. Thanks, James

Mynda Treacy

Hi James,

Excel can only handle negative time when it uses the 1904 date system. You need to change the setting of the workbook in the Options.

Excel 2007: Windows button > Excel Options > Advanced > When calculating this workbook > Use 1904 date system.

Excel 2010: File > Options > Advanced > When calculating this workbook > Use 1904 date system.

Be careful though as changing this setting may have a knock on effect to other time and date calculations.

Kind regards,

Mynda.

Jer

Hi,

i fast can i receive the 102 tricks videos for office excel (+ free outlook and video) upon purchasing?

Can i receive it immediately, at most within 3 days upon purchase confirmation on the website?

Appreciate your immediate feedback.

Thanks,

Jer

Mynda Treacy

Hi Jer,

Once you have paid you will be sent an email with the link so that you can download the videos. This is usually within less than 24 hours.

Kind regards,

Mynda.

Donna

Hi there! I have a question for you. I am looking for an office management /customer service position in which I have not done since 2004. I am now finding that most positions require excel and word. Will this training course teach me what I need to know so that I can feel confidant in applying for work?

Thank you, Donna

Mynda Treacy

Hi Donna,

Thanks for your question.

Yes, our training courses will teach you the ins and outs of Word and Excel so you can feel confident in applying for work. And with a 12 month membership you will be able to refer back to the tutorials any time if you get stuck with something. After all we donβt remember everything we learn first time.

Just remember, the most important thing in learning anything new is to practice to help you retain it. To support you in this you can download the workbooks, (which are what was used in the filming of the videos) and re-create what you learn.

I hope that answers your question and good luck with your search for work. Please let me know if I can help you further.

Kind regards,

Mynda Treacy

Donna

Mynda, Thank you for responding back. You have given me the encouragement to give this a try!

Many thanks,

Donna

Mynda Treacy

You’re welcome, Donna.

Be sure to get in touch if I can help you further.

Kind regards,

Mynda.

Effie

Hi! I have been looking on your website and the training package you provide for word, excel and outlook. I was just wondering, do you provide also any kind of certification after the completion of the courses?

Thank you in advance for your answer.

Mynda Treacy

Hi Effie,

Thanks for your question. Yes, our courses come with a certificate of completion π

Kind regards,

Mynda.

Joanne Fogel

I wish you guys did training in 2003 Microsft suite – but you guys are very up to date and that is good for you of course but bad for me. I am looking for work and I must get some training in Microsoft suite 2003, I have only had one day workshops. Altho I have used the newer versions at work, I was only able to get the 03 version for free from my sis, cannot afford anything now Im outa work.

JoanneF

Mynda Treacy

Sorry we can’t help you with 2003, Joanne. With the next version of Microsoft Office due for release mid 2012 the demand for 2003 training is diminishing by the day. You can download trial versions of Office 2010 that will work for 30 days at http://office.microsoft.com/en-au/try/

I hope that helps.

Mynda.

Jeremy

Thanks Mynda – thats great – I think we will buy, expect a sale shortly! cheers

Jeremy

Hi, are you able to let me know exactly how a Microsoft Office subscirption would work? My Wife used to be a legal secretary but is now a stay at home mum until the kids grow up a bit more. To ensure we continue to recieve the Childcare rebate, she needs to continue to undertake some form of skill training every week (ie an hour). I thought the microsoft training subscription might be ideal but just want to check exactly how this subscription actually works in practicality. Are you able to advise?

Many thanks

Jeremy

Mynda

Hi Jeremy,

Thanks for your question.

The way the subscription works currently is you pay once and you have full access to the complete training library for life. You can access the training videos at any time 24/7. You just need an internet connection and speakers or headphones plugged into your PC.

I hope that answers your question. Please let me know if I can help further.

Kind regards,

Mynda.