Best Online Microsoft Excel Training Class For Beginners and Professionals in Northamptonshire England 2018-07-28T19:08:28+00:00

Online Microsoft Excel Training Courses Northamptonshire England

Microsoft Excel Courses Online_

As of late employers are looking for not only college degrees but also extra skills. As the leading provider of Excel Training Classes in Northamptonshire England, Earn and Excel is very aware of this. A simple and affordable way to add eye-catching content to your resume is by having advanced Microsoft Excel training. There are many explanations why you could advance a career with MS Excel. If you don’t know anything about Excel, then you need to be trained how to use it soon. With that said, let us chat about many of the reasons, and how, you could improve your career through learning Microsoft Excel. Even though there are other options Microsoft Excel it is still the best choice for many medium to small businesses throughout Northamptonshire England.

Why you should take Microsoft Excel Training Classes?

First and foremost, it’s an extremely popular skill. Figuring out how to use Excel Program means you will be on your way to having a highly desired skill. You might be blown away at the amount of companies in all types of industries count on Excel at some level or another. The truth is, some companies have branches where employees only use Microsoft Excel in their day to day function. They employ workers who monitor anything from finances to simple business dealings and any other vitalinformation. When you know how to utilize MS Excel you will possess an sought-after ability. The truth is that most supervisors do not have the time to do their particular tasks using Excel. This is why they employ men and women who are skilled in it.

Identify Trends with Excel Program

The longer you make use of Excel Program, the more improved you’ll get at recognizing evolving trends. Most firms realize that staff who frequently use Excel Program are excellent at pointing out trends, which might ultimately lead to career growth. For example, if you work for a corporation and you begin using Excel Program, and begin seeing trends, then you might get a promotion, pay increase or a new job function might be created for you. In addition to that, being able to point out trends can help a company be more successful. It might even help them alter or tweak their strategy. In such a circumstance, if you are the individual that is able to identify trends, then you can certainly bet there is a good chance that your company will repay you.

Searching for Microsoft Excel Training Courses in Northamptonshire England?

At Earn and Excel is not the only from offering Excel Training Classes in Northamptonshire England. MS Excel is not a difficult software to learn. A lot of people will get it in just a few lessons. However, like with everything in life not all online Excel Training Classes are equal. Several of our students have complained about the lack of advanced training other courses have. The Earn and Excel Microsoft Excel Training Courses were put together to help you be more attractive to employers. This means learning features like data tracking!

Tracking project data and bringing it together in a way that is practical and easy to understand is undoubtedly an invaluable skill, particularly if you work at a place where there are many other team members or partners. By knowing how to properly and effectively track data and lay it all out within an easy-to-understand format may help advance your career. Among the best aspects of Excel is you can use it to take various types of data together, for example documents, files and even images. Once you figure out how to use Excel, you’ll eventually learn how to do those ideas.

Getting ahead in your career with Excel is feasible with the right training. Besides data tracking, creating charts is yet, another highly desirable skills. If you learn how to build charts in Excel that means that you can get the job anywhere from a data analytics company to a financial advising firm. There are many types of Excel charts you may build, and you may impress your boss or perhaps the company you want to help by creating charts. For example, if you have an interview using a company, then you can develop a sample chart in accordance with the nature from the work they do. This might adequately increase your chances of having the job and advancing with your position.

So, the question is – Are you ready to succeed career with Excel? Whether you are brand new with it or maybe you have some experience, you ought to become as proficient with Excel as you can be. The sooner you perfect Microsoft Excel, the sooner you’ll advance with your position. When you are looking for more info about Earn & Excel’s top rated online Microsoft Excel training classes Northamptonshire England stop by our blog

Microsoft Excel Training Course in Northamptonshire England Related Blog

How Do I Filter Records in Excel?

Excel Pivot Table Training_

Nowadays, Excel files are loaded with numerous records. In such cases, it is very difficult to find specific information quickly. My Excel classes are here to help you learn how to most efficiently navigate these spreadsheets. The Excel filter will help hide unwanted records and lets us view only necessary records on the worksheet.

Can you Filter Records with Excel’s Built-In Filter?

Before using the Excel filter, make sure that your data range has headers. The filter will be applied to each column and the header row will be used to identify the column name. Unlike with other elements we’ve learned about in your Excel training, Excel will not throw an error if the header row is missing. It will still continue to apply filter drop downs on the first row in your data range. Follow the steps mentioned below to apply the filter to records on an Excel worksheet:

Step 1: On the Excel ribbon, select the tab “data”. Under the group, “sort & filter”, click on “filter”. Now each column in the header row will have a drop-down arrow. Please note, before clicking the option “filter,” to make sure that there are no blank rows in between records. If there is a blank row, the filter will be applied only to records that are above the blank row and the rows below the blank row will be skipped from filtering.

Step 2: If you want to include blank records, you must manually select the entire data range and then apply the filter. If you do so, Excel will treat blank as a value and in the filter drop down, you could see blank as a filter criterion.

Step 3: Excel automatically sees the data type of each column in your data range. Based on the data type, the filter type varies on each column. On a column that contains text, you would see Text filters, whereas on a column with numbers you would see number filters. Similarly, on a column with dates, you would see date filters. Excel’s date filter allows you to filter your data using 35 different options. The table below gives a detailed list of those options. We strongly recommend referring to this as you become more and more familiar with the content of this Excel tutorial.

1.       Equals

2.       Before

3.       After

4.       Between

5.       Tomorrow

6.       Today

7.       Yesterday

8.       Next week

9.       Last week

10.   Next month

11.   This month

12.   Next quarter

13.   This quarter

14.   Last quarter

15.   Next year

16.   This year

17.   Last year

18.   Year to date

19.   Quarter 1

20.   Quarter 2

21.   Quarter 3

22.   Quarter 4

23.   January

24.   February

25.   March

26.   April

27.   May

28.   June

29.   July

30.   August

31.   September

32.   October

33.   November

34.   December

35.   Custom filter

Excel’s number filter allows you to filter your data using 11 different options. The table below gives a detailed list of those options.

·         Equals

·         Does not equal

·         Greater than

·         Greater than or equal to

·         Less than

·         Less than or equal to

·         Between

·         Top 10

·         Above average

·         Below average

·         Custom Filter

Excel’s text filter allows you to filter your data using 7 different options. The table below gives a detailed list of those options.

  1. Equals
  2. Does Not Equal
  1. Begins with
  2. Ends with
  1. Contains
  2. Does not contain
7.       Custom Filter

The filter can be applied on a single column or multiple columns. As you the apply the filter, you can see that the records that do not meet the filter criteria are hidden.

Why Excel Shows Text Filter For Columns Containing Dates

Sometimes Excel will show text filters on columns that contain dates and this is because of the following reasons:
1) Users might have formatted dates and saved them in cells as text.
2) The cell into which date was entered has text format.
3) When the Excel sheet was generated, all the data entered was in text format.

We must convert those columns into the correct datatype so that we can view and apply an appropriate filter over such columns. For changing dates which are in text format into a proper date format, you can use Excel’s date value function. You must pass the date in text format to this function and it will return a serial number. Using the datevalue function in an empty column, pass the first cell in the range as a parameter to the function. Then, using fill handle, apply this to the entire range. Do not forget to switch the format to date on the new column.

If you are not comfortable with the manual conversion of dates in text format to actual date format, then use the query below.

As per this query, Column V contains the date in text format. This query first finds the last used row in Column V and then converts every record into date format. This macro does not require a new column as the converted data replaces the existing data. However, if you are using the manual method, you have to create a new column.

Sub TexttoDate()

Dim r As Long

Dim lr As Long

lr = ActiveSheet.Range(“V” & Rows.Count).End(xlUp).Row

For r = 2 To lr

ActiveSheet.Range(“V” & r).Value = VBA.DateValue(ActiveSheet.Range(“V” & r).Value)

Next r

End Sub

The script below will help you to convert text to numbers. This script multiplies the value in the cell by 1 and then converts the format of the cell to general.

Sub TexttoNum()

Dim r As Long

Dim lr As Long

lr = ActiveSheet.Range(“V” & Rows.Count).End(xlUp).Row

For r = 2 To lr

ActiveSheet.Range(“V” & r).Value = ActiveSheet.Range(“V” & r).Value * 1

ActiveSheet.Range(“V” & r).NumberFormat = “General”

Next r

End Sub

End Sub

Manage Records That You’ve Applied the Excel Filter To

Unlike Excel’s advanced filter that has an inbuilt option to copy filtered records to a new destination (which we will discuss in another part of your Excel classes), the Filter option does not have options to manage filtered records. To copy filtered records to a new destination in the worksheet, select “find & select” from the Home tab on the Excel ribbon. Then click on “go to special”.

A form will pop up. Select “visible cells only” and then click on OK. Excel will now select visible records which you can easily copy and paste into a new sheet using ctrl+c and ctrl+v.

Is the Excel Filter Automatic?

Consider you have applied the text filter on a column with the condition “begins with ‘A’”. After the filter, the column will hold records that begin only with the letter ‘A’. However, if you change any of the filtered records to begin with a letter other than ‘A’, Excel will not automatically identify this, and it will not hide this record since this record has not been passed to the filter criteria. You must manually reapply the filter. To do that, from the Excel ribbon that we’ve referenced in nearly all of our Excel training materials, click on the Tab “data”. Then from the group, “sort & filter”, click the option “reapply”.

Can You Reapply an Excel Filter Using VBA?

Using the option “reapply” from the Excel ribbon is a manual task. Using a macro, we can automate it. The benefits of using a macro instead of manual entry can be seen in the avoiding shortcuts content contained within this Excel class.

Step 1: Within your Excel workbook, activate the worksheet that has a filter applied to your data set.

Step 2: Right-click on the worksheet tab and from the list of the visible context menu, click “view code”. This will open the Visual-basic editor

Step 3: Paste this code into the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.AutoFilter.ApplyFilter

End Sub

Step 4: Save your workbook as macro-enabled Excel file.

Step 5: Now, for any change you make on the filtered record, Excel will automatically track the change and will reapply the filter.

Remember, if you are unsure about any advanced features on Excel, it is best to enter into additional online Excel courses to learn the best practices.

Option 2: Advanced Filter

Step 1: Before using Excel’s advanced filter, you might have to set up your data range. Make sure that your data has a column header and each header has a unique name. If names in your header are duplicated, Excel’s advanced filter will throw an error.  You should also ensure that there are no blank records in between your data. If there are blank records, delete them manually or send them to the last row by sorting records.

Step 2: Next step in using Excel’s advanced filter is to set up the range as a criterion. Though this is optional, this is what differentiates Excel’s advanced filter from the conventional filter. You can set a range on any other worksheet as criteria to your advanced filter setup. However, it would be very easy if the criteria range is just above the data set.

If your first record in your data set starts at row 1, then insert new rows at the top and push your data set below. Copy column headers from your dataset and paste it as column headers to your filter criteria range. Always ensure that column headers of your data set and column headers of the criteria range are the same. A mismatch in the column header will hide all records in your data set.

Step 3: We are now ready to apply the advanced filter. After selecting any cell in your dataset, on your Excel ribbon, select the tab “data”. From the group, “sort & filter”, click “advanced”. This will bring up a user form. In the user form under action, the option “filter the list, in-place” will apply the filter on the active sheet. You can then copy filtered records to a different sheet using the option “copy to another location”. The three text boxes on this form will allow you to define the range of your data set, the range of the filter criteria, and the new destination to which your records should be copied after the filter is applied. The option “unique records only” will help you to remove duplicates from filtered records. After choosing your options, press “OK” on the form and your data will be filtered.

Is The Advanced Filter Automatic?

 The advanced filter will not pick up changes that you make to your data set or to the criteria change. After making changes, you must again click on the option “Advanced” from the Excel. This time, when the form opens, all your previous values will reappear. You must press “Ok” to reapply the filter.

Reapply Advanced Filter Using VBA

Using VBA, another topic discussed in another section of my online Excel training materials, we can automatically refresh the filter criteria.

Step 1: Within your Excel workbook, activate the worksheet that has an advanced filter applied on your data set.

Step 2: Right-click on the worksheet tab and from the list of the visible context menu, click “view code”. This will open the visual-basic editor.

Step 3: Paste this code into the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)

Range(“<Data Range>”).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range(“<Criteria Range>”), Unique:=False

End Sub

Do not forget to update placeholders <data range> and <criteria range> with actual values. Now for any change that you make to your data set or to your criteria range, the advanced filter will be reapplied.

How Can You Use Operators and Wildcards in Advanced Excel Filter?

The table below lists operators and wildcards that can be used in the criteria range.

Operator SymbolDescriptionApplies to
<less thanNumbers, Dates
<=less than or equal toNumbers, Dates
>=greater than or equal toNumbers, Dates
<>not equal toNumbers, Dates
Wildcard SymbolDescriptionApplies to
*asteriskText
?question markText
~tildeText

The wildcard * informs Excel that it can be any character and any number of characters. In the image below, text at A2 is a criterion for the auto filter on the data set below it. The value “*ani*” will filter the dataset and will show records that have “ani” in it.

The wildcard ? informs Excel that it can be any single character.

With the wildcard ~, you would be able to filter records that contain another wildcard. For example, if the text in a column contains the symbol “*”, which is a wildcard for advanced filter criteria, you can filter records by making the criteria look like this <text>~*<text>. Replace the placeholder <text> with appropriate values.

If =”=?????” is the criteria value, it filters dataset with columns values whose length does not exceed 5.

If =”=text” is the criteria value, it filters dataset with column values that match exactly with the criteria value.

How Can You Create Conditions in Advanced Filter?

If you list multiple criteria in the same row, you create an ‘and’ condition. According to the image below, the data set is filtered using two conditions.

Condition 1: The region should have the text “Asia.” Any character and any number of character can be present before and after the text “Asia.”

Condition 2: The population should be greater than 500,000.

Records that pass both these conditions will be visible on the sheet and records that do not pass both these conditions will be hidden.

If you list multiple criteria in a different row, you create an “or” condition. According to the image below, records that have the text “Asia” in the column “Region” or records that have a population greater than 500,000 passes the filter criteria and will be visible on the sheet.

How Can You Create An Excel Filter Using VBA?

You can use a macro to filter large data sets. Here are some macros that you can paste into a new module and invoke to filter records. Replace placeholders <data range> and <criteria value> with appropriate values.

This script applies text filter to the field1 in the data set. Records that begin with the criteria value pass the filter criteria.

Sub Filter_BeginsWith()

Selection.AutoFilter

ActiveSheet.Range(“<Data Range>”).AutoFilter Field:=1, Criteria1:=”=<Criteria Value>*”, _

Operator:=xlAnd

End Sub

This script applies text filter to the field1 in the data set. Records that contain the criteria value pass the filter criteria.

Sub Filter_Contains()

ActiveSheet.Range(“<Data Range>”).AutoFilter Field:=1, Criteria1:=”=*<Criteria Value>*”, _

Operator:=xlAnd

End Sub

This script applies text filter to the field1 in the data set. Records that do not contain the criteria value pass the filter criteria.

Sub Filter_DoesNotContain()

ActiveSheet.Range(“<Data Range>”).AutoFilter Field:=1, Criteria1:=”<>*<Criteria Value>*” _

, Operator:=xlAnd

End Sub

This script applies text filter to the field1 in the data set. Records that end with the criteria value pass the filter criteria.

Sub Filter_EndsWith()

ActiveSheet.Range(“<Data Range>”).AutoFilter Field:=1, Criteria1:=”=*<Criteria Value>”, _

Operator:=xlAnd

End Sub

If you are using VBA to filter records in a worksheet, it is very important to identify if the sheet already has a filter applied. Copy this script into your workbook and run the script. This will identify if the active sheet has a filter on it.

Option Explicit

Sub Check_Filter()

If ActiveSheet.AutoFilterMode Then

Debug.Print “Yes, this sheet already has AutoFilter”

Else

Debug.Print “I am not able to find AutoFilter on this sheet”

End If

End Sub

This script will remove filters applied on a sheet and will display all records on it.

Sub Dissolve_Filter()

On Error Resume Next

ActiveSheet.ShowAllData

On Error GoTo 0

End Sub

With these tips, you should be able to use the Excel Filter functions to enhance your Excel experience. If not, you might need to undertake further Excel lessons.

Take Advanced Excel Classes to Master More Complex Material

As you can see by reading all of the above information about filtering records in Excel, there’s a lot to cover! There are many ways to accomplish your filtering goals, though each method has its own advantages and pitfalls. Participation in one of my Advanced Excel classes will help increase your familiarity and understanding of this relatively complicated function of this well-loved program.