Thursday, 18 October 2018

Microsoft Excel - Additional Sort Options



Additional Sort Options.

1. Add 

2. Delete Level: If you wish to remove a sort level, select the level you wish to remove a click on the Delete Level button.

3. Copy Level: Will allow you to duplicate a sort level, select the level you wish to duplicate and click Copy Level.



4. Move up Down: This will allow you to change the sort order priority.

5. Options: Addition sort options
By default Excel is not case sensitive, select this option if required
The default sort orientation is set to up & down the column, this new feature allows sort Left & Right on the row.



6. My data has headers: Normally a data table would have headings above the data to indicate what the contents of the columns contain.
The column headings do not get sorted with the rest of the data.



if your table does not have headings remove the tock form the box.

Microsoft Excel Advanced Filter


Advanced Filter

The Advanced Filter is similar to the Autofilter, the Autofilter is fully automated whereas the Advanced filter we have to set up manually with the help of an Excel wizard.

The benefit of the Advanced Filter is that you can enter more complex criteria(s), the data can be filtered in-place or copied to another sheet.


Setting up the Advanced Filter.

1.     Copy the header row of your list and paste one set of headers to the right hand side of the list, separating them by at least one column or row from the list, This will act as your criteria headings.


2.     Enter the Criteria values.




Once you have entered all the criterias that you wish to extract from the List, you are ready to start the Advanced Filter wizard.

1.   Select from the Data tab the             
Advanced Button.


Tip: Place your cursor within the List Range before you start the wizard, this will save you highlighting the range in the wizard.






Select the Copy to another location if you wish to place the filtered data either beside or on another sheet, if this option is not selected the data will be filtered in place (the original List will be hidden).

List Range: The List range box will be automatically completed.

Criteria Range: Highlight the criteria including the headings.




Copy to: Select the location where you wish to place the results (filtered data “Extract”).

This option will not be able to be selected unless you have chosen the Copy to another location.

Unique records only: If you origional list has duplicate records, tick this box so that duplicates records are not shown.



Reserved Names


You can name a range "Criteria", and the reference for the range will appear automatically in the Criteria range box. Define the name "Database" for the range of data to be filtered and define the name "Extract" for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes. Advanced filter is advanced in years as well, this is the old, original design of the Excel data filter from the early 1990's long

before AutoFilter arrived.


Database Functions.

There is a range of database functions that can be performed with the advanced filter, below we have listed a few of the more frequently used database functions.

Firstly we must understand the syntax of the function, to show this we are using the DSUM function using the above advanced filter example.














Microsoft Excel Applying Design Style



Applying a Design Style
Once ou have created the pivot table report, it is possible to add a design Style, this will enhance the presentation.


1.     Select PivotTable Tools .Options.   

                  
2.  Click on the Select button, this will give you various selection options.

Once you have selected the option required, the pivot table will highlight the area.




3.     Click on the Design tab on the ribbon.

Select the required Design Style.