Lessons Archive

What can go wrong with a copy/ paste

Free Preview

Length: 11 minutesComplexity: Easy

Copy/ Paste is one of Excel's best features. But be aware of what can go wrong, especially when it comes to absolute and relative referencing. Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

The risks with inserting columns and rows (and how to spot them)

Free Preview

Length: 11 minutesComplexity: Easy

Whenever you insert columns or rows, make sure you test the formula using those rows and columns. Has Excel correctly included them in the calculation? Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Errors that arise from linking spreadsheets together

Free Preview

Length: 11 minutesComplexity: Easy

Excel spreadsheets can be linked together and is probably one of the main reasons Excel is so popular. But be very careful with this. It is extremely easy to make a mistake. Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

What can go wrong with hidden rows and columns

Free Preview

Length: 11 minutesComplexity: Easy

Hidden rows and columns may appear useful but there are major risks with this. Rather use this suggestion to reduce the risk. Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Use Excel’s inbuilt tools and functions

Free Preview

Length: 11 minutesComplexity: Easy

Excel has been built and tested thoroughly by the experts at Microsoft. Furthermore it is tested everyday by users like yourself and Microsoft gets told about any issues with its tools or functions. As much as possible try and use Excel's tools and functions and don't create your own. If you do, make sure you test them thoroughly!

Don’t insert or delete a cell (or at least be very careful)

Free Preview

Length: 11 minutesComplexity: Easy

Have you ever had a formula that was perfect, and suddenly, for no apparent reason it looks to the right or down? Perhaps you did this? Similarly, have you ever done something and suddenly one or a few (not all cells) have a REF error. The REF error cells need to be fixed, but watch out for the cells to the right or below as well. Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Don’t insert or delete a cell (or at least be very careful)

Free Preview

Length: 11 minutesComplexity: Easy

Have you ever had a formula that was perfect, and suddenly, for no apparent reason it looks to the right or down? Perhaps you did this? Similarly, have you ever done something and suddenly one or a few (not all cells) have a REF error. The REF error cells need to be fixed, but watch out for the cells to the right or below as well. Watch the video for more. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Fill in all the blanks

Length: 10 minutesComplexity: Easy

When you get reports, you sometimes need to 'fix' them so that you have no blanks and each line has all the information. Below a report, but we need to fill in the blanks i.e. London should appear in A3 to A7. We could spend all day copy and pasting each one down or else we can use the GOTO Special tool. <img class="aligncenter size-full wp-image-8849" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/07/GoTo-Special-15.png" alt="GoTo Special" width="992" height="508" /> See below, all the blank cells are told to look at the cell above it and you have the blanks filled in. <img class="aligncenter size-full wp-image-8851" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/07/GoTo-Special-17.png" alt="GoTo Special " width="631" height="489" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.

Beginner MS Excel

Free Preview

Length: 5 minutesComplexity: Easy

Details about the Online Beginner MS Excel portion of the Course. With over 60 lessons, 130 video clips, and 28 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course will give you the fundamentals to make sure it is easy to learn anything about Excel.

Data Cleanup in Excel Course

Free Preview

Length: 5 minutesComplexity: Easy

No matter what your client says, the data you get is never in EXACTLY the correct format. You will need to do some cleaning up and scrubbing to make it useable. This course shows you all the tips and tricks with cleaning up data.

Excel Dashboards and Management Reporting Course

Free Preview

Length: 5 minutesComplexity: Easy

Once you know what you are trying to tell your client, you need to be able to convert it to meaningful, reusable reports and visualisation to help the client understand the issues. This course shows all the tips on how to build Excel dashboards and management reports.

PowerQuery Course

Free Preview

Length: 5 minutesComplexity: Easy

PowerQuery is the free new tool in Excel that is built for data cleanup issues. It solves all the problems that you thought you needed VBA for and makes it easy to automate regular data cleanup processes.

Intermediate MS Excel

Free Preview

Length: 5 minutesComplexity: Easy

Details about the Intermediate MS Excel Online portion of this bundled course.  With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.

Advanced MS Excel

Free Preview

Length: 5 minutesComplexity: Standard

Details about the Advanced MS Excel Online portion of the course bundle. This course has over 141 lessons, 240 video clips and 68 example exercises with detailed video solutions explaining exactly how you should have got to the answer. The final portion of the course takes your Intermediate knowledge and goes one step further, solving more complex Excel problems with tools and a particular focus on the functions and formula you will need to use.

Financial Modelling with Excel

Free Preview

Length: 5 minutesComplexity: Easy

Details about the Financial Modelling with MS Excel Online portion of this bundled course.  With over 115 lessons, 200 video clips and 45 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Advanced knowledge from the previous course and teaches you to solve problems in Excel with a particular focus financial modelling, budgeting and forecasting.

Financial Modelling with Excel

Free Preview

Length: 5 minutesComplexity: Easy

Details about the Financial Modelling with MS Excel Online portion of this bundled course.  With over 115 lessons, 200 video clips and 45 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Advanced knowledge from the previous course and teaches you to solve problems in Excel with a particular focus financial modelling, budgeting and forecasting.

Intermediate MS Excel

Free Preview

Length: 5 minutesComplexity: Easy

Details about the Intermediate MS Excel Online portion of this bundled course.  With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.

Advanced MS Excel

Free Preview

Length: 5 minutesComplexity: Standard

Details about the Advanced MS Excel Online portion of the course bundle. This course has over 141 lessons, 240 video clips and 68 example exercises with detailed video solutions explaining exactly how you should have got to the answer. The final portion of the course takes your Intermediate knowledge and goes one step further, solving more complex Excel problems with tools and a particular focus on the functions and formula you will need to use.

About the ‘PowerQuery Course’

Free Preview

Length: 5 minutesComplexity: Easy

PowerQuery is the free new tool in Excel that is built for data cleanup issues. It solves all the problems that you thought you needed VBA for and makes it easy to automate regular data cleanup processes.

About the ‘Excel Dashboards and Management Reporting Course’

Free Preview

Length: 5 minutesComplexity: Easy

Once you know what you are trying to tell your client, you need to be able to convert it to meaningful, reusable reports and visualisation to help the client understand the issues. This course shows all the tips on how to build Excel dashboards and management reports.

About the ‘Pivot Tables in Excel Course’

Free Preview

Length: 5 minutesComplexity: Easy

Before you can present any useful findings, you will need to be able to (easily) play with the data to see what the trends are, where the anomalies are, and determine which reports are meaningful. Pivot Tables are the ideal way to quickly and easily create reports and look at getting true business intelligence out of them.

About the ‘Data Cleanup in Excel Course’

Free Preview

Length: 5 minutesComplexity: Easy

No matter what your client says, the data you get is never in EXACTLY the correct format. You will need to do some cleaning up and scrubbing to make it useable. This course shows you all the tips and tricks with cleaning up data.

Intermediate MS Excel

Free Preview

Length: 960 minutesComplexity: Standard

Details about the Intermediate MS Excel Online portion of this bundled course.  With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.

Advanced MS Excel

Free Preview

Length: 960 minutesComplexity: Standard

Details about the Advanced MS Excel Online portion of the course bundle. This course has over 141 lessons, 240 video clips and 68 example exercises with detailed video solutions explaining exactly how you should have got to the answer. The final portion of the course takes your Intermediate knowledge and goes one step further, solving more complex Excel problems with tools and a particular focus on the functions and formula you will need to use.

Pivot Examples: Business Intelligence 2

Complexity: Easy

Pivot Examples: Business Intelligence Now using the data available and what you have learnt, answer the following questions about this data. You can do it whatever way you want. 6- Graph the calls made over the days of the month i.e. calls on the 1st of the month, 2nd etc to see if calls are mostly made at the beginning or end of month (there is a day of month field already in the database). 7- On what day was a baby born (Clue- lots of SMS’s are sent to announce the baby- note the date as you will be asked it in the MCQ at the end) 8- How much Net Profit does the provider make off me for calls and sms’s in total over the period (Use the Net Profit Field) 9- How much Net Profit does the provider make per month 10- How much Net Profit does the provider make per month AND based on each destination (e.g. calls to Vodacom, calls to Cell C etc). To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.

Conditional Formatting- Exercises 2

Complexity: Easy

<h2>Exercise 7</h2> We have a table of data showing how much ore is in each location. We also have a table showing where we have already mined. <ul> <li>In cells C6 to V25, use a colour scale to create a heat map of where the best locations are</li> <li>In these same cells, use a formula conditional formatting to show which locations have already been mined (black it out)</li> <li>In X6 to X25, create a formula to determine how much ore is still available to be mined (note that in column Y we have shown the total ore available).</li> <li>In Z6 to Z25 show the percentage ore remaining.</li> </ul> <h2>Exercise 8</h2> We want to create a Gantt type chart but don’t want to use a chart <ul> <li>In J8 to AL9, create conditional formats that <ul> <li>Will show when we start and end with land (use a light green shading)</li> <li>When Bulk will start and end (use pattern)</li> <li>When Internals start and end (change the borders to be red)</li> </ul> </li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Date Examples 4

Length: 11 minutesComplexity: Easy

<h2>DATE Ex 14</h2> You need to determine if a particular year is a leap year based on the years in the blue blocks Steps 1.     In Column D (Yellow cells), create the leap year date for the year chosen e.g. 29/02/1998, 29/02/1999 etc . 2.     In column E, check whether Excel believes that this is the correct date for a leap year. You can do this by extracting the MONTH out of the dates generated in Column D 3.     Build an IF statement to check whether the relevant year is a leap year. <h3>Solution</h3> Refer to the solution page <h2>DATE Ex 15</h2> Based on the date given, determine which quarter the date falls into. In the first instance it is normal quarters (i.e. Jan to Mar is Qtr 1, April to June is Qtr 2 etc). In the second instance the quarters are based on year end so Mar to May are Qtr 1, June to Aug are Qtr 2 etc. Steps 1.     In D12 down, determine the month of the date in Column B 2.     In E12 down, calculate the quarter being referred to. You can use an IF statement or try and find a function using the Function Wizard that allows you to roundup in multiples of a number, in this case 3. 3.     In D21 down, determine the month of the date in Column B 4.     In E21 down, calculate the quarter based on the table in G22 to H33. If you already know the VLOOKUP formula you can use that. Otherwise, try out the CHOOSE formula (look in your function wizard to find it) <h3>Solution</h3> Refer to the solution page <h2>DATE Ex 16</h2> You want to know the last weekday (Monday to Friday) in any month based on the dates in cells B7 to B9. Steps 1.     In column D, determine the last day of the month 2.     In column E, find out what day of the week the last date is 3.     In column F calculate the maximum weekday allowed for our purposes (so if the day is a Saturday or a Sunday, it must show as a Friday here) 4.     In column G, calculate how many days (if any) you must subtract from the last day of the month to get the last weekday. 5.     In column H generate the date of the last weekday 6.     In column I create a check to make sure that the date in Column H is a weekday. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Date Examples 3

Length: 11 minutesComplexity: Easy

<h2>DATE Ex 11</h2> You need to determine the average days to deliver goods. <ul> <li>In columns C and D you have extracted the Invoice month but the month and year are shown in separate columns. In column E (yellow cells) you need to create a valid Excel date with the day always assumed to be the 1st day of the month.</li> <li>In column H you have received the Delivery Date but in a format that Excel doesn’t recognize as a date. In column I, copy and paste column H and then use a tool in Excel to convert the dates into a valid Excel date.</li> <li>In Column J, calculate the number of days between invoice date (column E) and delivery date (column I).</li> <li>In cell J9, work out the average of the delivery days.</li> <li>In Column L, determine what day of the week the delivery occurred on (based on column I). In column M set up a formula to say either weekday or weekend depending on the result in column L.</li> <li> cell M5 count how many deliveries happen on a weekend. In M6 count how many happen on a weekday.</li> <li>In N5 & N6, determine the percentage of deliveries that happen on a weekday and weekend.</li> <li>In N9, calculate what is the AVERAGE IF the delivery is on a weekend.</li> </ul> <h2>DATE Ex 12</h2> You need to determine the average days to deliver goods. <ul> <li>In columns C and D you have extracted the Invoice month but the month and year are shown in separate columns. In column E (yellow cells) you need to create a valid Excel date with the day always assumed to be the 1st day of the month.</li> <li>In column H you have received the Delivery Date but in a format that Excel doesn’t recognize as a date. In columns K to Q you must create formula to convert the delivery date into an Excel recognizable date. To do this do the following: <ul> <li>In column K you need to FIND where the first full stop occurs.</li> <li>In Column L you need to FIND where the second full stop occurs.</li> <li>In Column M, you need to calculate the gap between the full stops.</li> <li>In Column N, O, and P extract the Day, Month and Year from column H..</li> <li>In Column Q, convert the information in column N, O, P into a valid DATE.</li> <li>In column R, calculate the number of days from invoice to delivery.</li> </ul> </li> <li>In cell R77, calculate the average number of days till delivery and compare to the previous example (should be the same)</li> </ul> <h2>DATE Ex 13</h2> You need to build a spreadsheet that calculates the monthly rentals on a number of leases. You are provided with the rental, the escalation dates, and escalation percentage. Build formulas in the yellow cells so that your total at the bottom can match the suggested total. Steps: 1-    Extract the month of escalation into the cells E5 to E9 (e.g. an August escalation will say 8) 2-    In row 17, extract the month from the date in the row below (e.g. September 2009 will show a 9) 3-    In the rows 19 to 23, create a formula that will compare the current month to the escalation month for the relevant lease. If it is the escalation month then the rent must increase by the escalation %. If not it must just use the month before. 4-    If you have created the formulas in rows 19 to 23 row by row, try and change the formula in cell F19 so that you can copy it down and across and all the calculations are still correct. This involves making sure the absolute and relative referencing is correct. You are now told that the leases are for student accommodation and as a result there is no rent collected in December and January while the students are on leave. 5-    Open up the hidden rows. 6-    You have the input cells for the months of vacancy in C31 and C32. 7-    Create a formula in row 34 (yellow cells) that will determine if the current month is one of the vacant months. If it is vacant then it must show 0%. If not then it must show 100% 8-    Complete the formula in rows 38 to 42 to multiply the rental with the occupancy. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Date Examples 2

Length: 11 minutesComplexity: Easy

<h2>DATE Ex 8</h2> This is a cell phone bill for a number of months. We want to analyse the call patterns of the person but before we can do that we need to manipulate some of the key data. Complete the formula in the yellow columns as follows. Steps to follow: 1.     In Column H create a lookup to bring through the name of the network dialed based on the table shown in cells R2 to S12 2.     In Column I calculate the day of the week (Monday =1 , Sunday =7) 3.     In Column J develop a formula that will say whether the day in Column I is a weekday or a weekend (Hint- anything bigger than a 5 is a weekend) 4.     In Column K calculate the month that the call relates to (January=1, December = 12) 5.     In column L determine the day of the month e.g. 25 December would give 25 6.     In column M develop a formula that will say whether the call was made before or after 19h00’s (the time formula are the same as dates. Look for the ones that will help you with this query). <h2>DATE Ex 9</h2> In column E we have an estimated time of arrival (ETA). In column D we have a range of the required time to arrivals (RTA). So 20.02-25.02 means it is required between 20 Feb and 25 Feb. You need to extract and convert to an Excel acceptable date (use 2012 for the year) the 3 dates i.e. the RTA1, RTA2 and ETZ. Once we have dates we need to determine which orders are going to be late by comparing the ETA to the RTA2 <h2>DATE Ex 10</h2> In column B, there seem to be some dates that Excel accepts and others that Excel is ignoring. We need to create formula to use the dates that Excel accepts and convert the other dates into an acceptable date format. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

SUMIF et al Examples 2

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 4</h2> In this example you are sending a file off to the bank to make the salary payments. However, you just want to check that all the bank account numbers are unique in case of finger trouble or worse phantom employees. Using a COUNTIF, in E7 column, create a formula that will check whether the bank account number in column B for the employee appears as anyone else’s bank number as well. <h2>SUMIFS et al Ex 5</h2> In our example we have a dump of the cash flow report from a system. You will notice that SUMIF a and SUMIF b have the same information but they are not on the same lines e.g. the Total Closing Bank Balance on SUMIF a is on row 38 and for SUMIF b it is on row 31. We want to determine the ratio of receipts to payments on row 55 but would normally have to create the formulas on each sheet to take into account the row numbers being inconsistent. We want formula in rows 46 to 51 that will search through the information and pull out the information we need. Once we have the information static, we can then run the ratios. Steps <ul> <li>On SUMIF a create a formula in D46 to J51 that will find and add the items described in C46 to C51 for each period.</li> <li>Once you have create this you should be able to copy and paste these formula into SUMIF b (same area) and it will work even though the rows with the relevant information are not identical.</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

VLOOKUP Examples 2- categorise, bands

Length: 20 minutesComplexity: Easy

<h2>VLOOKUP Ex 8</h2> Why is the VLOOKUP’s in J8 to L8 pulling through information relating to SP010 instead of the correct SP0101? Before you watch the solution, have a look again at the sort order of column A. Does it make sense to you? <h2>VLOOKUP Ex 9</h2> Create the following spreadsheet with the relevant inputs In the first yellow block, use a VLOOKUP to determine the interest rate to use. In the second yellow block calculate the interest earned To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Text Examples 4

Length: 10 minutesComplexity: Easy

<h2>Text Ex 12</h2> <ul> <li>Real life situation</li> <li>When you copy and paste say column A and B into a basic text editor like Notepad, the descriptions and numbers don’t align as shown below (try it)</li> </ul> <img class="aligncenter size-full wp-image-10183" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/11/Text_Ex_12_1.png" alt="Text_Ex_12" width="469" height="356" /> <ul> <li>We want to create a series of formula that will create a single column with the description and amount included in such a way that they always line up. Once you have that you should be able to copy and paste it into Notepad and see lined up numbers (like this).</li> </ul> <img class="aligncenter size-full wp-image-10182" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/11/Text_Ex_12_2.png" alt="Text_Ex_12" width="435" height="340" /> Steps 1.     Note cell E5 which specifies that the end result must have a total of 50 cells 2.     In column C, you need to convert the amount in column B into text (use the DOLLAR function) 3.     In column D, determine the number of characters in the description shown in column A 4.     In column E, determine the number of characters in the amount in column C 5.     In column F calculate how many spaces are required (you know how many characters are allowed as per Cell E5, and how many you need for the description and amount for each row) 6.     In column G create a formula that will generate a cell with the correct number of spaces as per column F’s calculation. You will need to search the function wizard (under the TEXT category) for a function that will repeat a blank x number of times. 7.     Join the Description (column A) with the spaces (column G) with the amount (column C) to create a single column with the exact spacing. If you want to now copy this into Notepad and see if it lines up. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Text Examples 3

Length: 10 minutesComplexity: Easy

<h2>Text Ex  9</h2> The IT system has extracted the employee’s names in separate columns for First Name and Surname. You need to combine them in another format for the payroll. However, there appear to be a lot of problems with the data with extra spacing, inverted columns, comma’s and other problem characters. We need to create something so that each month we can just paste in the dirty data and it converts it to clean data. <ul> <li>·         In Column C Combine the First Name and Surname into a single cell with a single space in between</li> <li>·         In cells D2 to I2 we have identified all the problem characters. In the yellow cells in column D to I, use the substitute formula to remove the unwanted character and replace it with nothing. Remember that each column should be based on the previous column so that the names get cleaner and cleaner.</li> </ul> If you discover other characters that need to be removed you can just add them to the next column. <h2>Text Ex 10</h2> You need to extract from the descriptions in cells B8 to B12, the date of the report to check whether they correspond with the month of the overall report shown in C6 Steps 1.     In C8 down, FIND where the first ‘/’ occurs 2.     In D8 down, extract the next 2 characters to pull out the month 3.     Depending on your settings you may need to convert the information in Column D into an Excel recognisable value in Column E (E8 down) 4.     In F8 down, check whether the month in column E is the same as the reporting month in C6 and say OK if it is and ERROR if it is not. <h2>Text Ex 11</h2> In this example we have extracted information from a PDF of phone records. We only want to extract the fax calls (has the words ‘Fax to EM’ in the text as per row 10. Furthermore, all we want to do is get the cost of the call which is the last few characters in the cell e.g. 6.09 in cell A10. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Text Examples 2

Length: 10 minutesComplexity: Easy

<h2>Text Ex 6</h2> Using a formula (not a tool), split column A so that we can see the account number and account description separately. <h2>Text Ex 7</h2> The IT system has extracted the employee’s names in a single column shown as SURNAME then FIRST NAME but you need it split between First Name and Surname and to clean up the presentation. <ul> <li>In column B change the case so that it appears in Proper Case i.e. FILLIES ANTHONY appears as Fillies Anthony.</li> <li>In Column D, FIND where the space occurs in the name (which tells us where the first name and surname split)</li> <li>In Column E, extract the First Name</li> <li>In column F extract the surname</li> </ul> <h2>Text Ex 8</h2> The IT system has extracted the employee’s names in a single column shown as SURMANE FIRST NAME but you need it split between First Name and Surname and clean up the presentation. In this case you have some names with more than one space (double barrel names) <ul> <li>In column B change the case so that it appears in Proper Case i.e. FILLIES ANTHONY appears as Fillies Anthony.</li> <li>In column C, find how many spaces are in column A. In order to do this think about using the LEN and Substitute function</li> <li>In Column D, find where the space occurs in the name (which tells us where the first word and second word split)</li> <li>In Column E, extract the First Name</li> <li>In column F extract the surname</li> <li>Switch on the Data Autofilter and investigate any row with more than 1 space. Make the correction in the data (this is unfortunately generally a manual process as double barrel names occur in first names, surnames and sometimes both).</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

IF examples 2

Length: 10 minutesComplexity: Easy

<h2>IF Ex 7</h2> We need to fill in the gaps in column A so that is always contain the sales sector (Government or Private). In cells K4 to Q49 create a formula that will look at column A, and if there is text there it will use it otherwise it will look above itself. <h2>IF Ex 8</h2> In the yellow cells you need to create a formula that will determine which interest rate to use based on the Bank Balance. You can either do it in one big formula or build it up in separate cells first. Below is a visual representation of the formula. <img class="aligncenter size-full wp-image-10172" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/11/Nested_If_Ex_8_1.png" alt="Nested If" width="675" height="180" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Absolute / Relative referencing- $ signs

Length: 10 minutesComplexity: Easy

You may wonder why, when you look at some spreadsheets, the calculations often refer to cells but there are $ signs in front of the letter and number.  This is Excel's way of addressing one of the problems you may have already experienced when copying and pasting calculations. <h2>Copy/ Paste once or Retype the formula a thousand times?</h2> As shown below, in order to calculate the tax amount we need to multiply the Net Profit (C19) with the tax rate (B21). We enter the formula as =C19*B21. <img class="aligncenter size-full wp-image-7361" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/01/23-Why-Dollar-Signs-1.png" alt="Why Dollar Signs" width="682" height="399" /> This will work fine. But when you want to copy this cell to the cell to the right you hit a problem. As shown below, what Excel will do is move  where it looks based on how you moved it. So when we copied cell C23 and pasted it into D23 (a shift of one column to the right), the cells within the formula also moved one column to the right. So where the formula was. =<span style="color: #ff0000">C</span>19*<span style="color: #ff0000">B</span>21 it now becomes =<span style="color: #ff0000">D</span>19*<span style="color: #ff0000">C</span>21 The move from C19 to D19 is ok because we need to look at the next month's profit. But the tax rate is in cell B21. Looking for it one column to the right is not going to give you the correct answer. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/beginner-excel-course/">here</a>.

Create a Pivot Table- Step by Step

Free Preview

Complexity: Easy

Create a Pivot Table- Step by Step including importing the data, introducing the data and rows, refreshing the data and changing the pivot reports

Advanced MS Excel

Free Preview

Length: 960 minutesComplexity: Standard

Details about the Advanced MS Excel Online portion of the course bundle. This course has over 141 lessons, 240 video clips and 68 example exercises with detailed video solutions explaining exactly how you should have got to the answer. The final portion of the course takes your Intermediate knowledge and goes one step further, solving more complex Excel problems with tools and a particular focus on the functions and formula you will need to use.