Advanced Filter in Microsoft Excel 2003

Advanced Filter is the most powerful way of filtering data in Excel. It takes a lot more work to set up than Autofilter, but goes beyond Autofilter's limitations.
The advantages to using Advanced Filter include:

• Run more complex filters
• Copy filtered results to another area on the worksheet
• Filter for unique records

The criteria that the Advanced Filter uses for its filtering are held in a separate range outside the range of data being filtered. This needs to be set up prior to using the Advanced Filter.

Apply an Advanced Filter

1. Copy the desired column labels from the range to be filtered to the first row of the criteria range
The example below is being setup to filter for the products in the list with a stock level of less than 20 and a stock value of more than £200. For this to work I would need the Stock Level and Stock Value labels in the criteria range.
In this example column labels have also been copied into cells H7:I7. This is optional, and is being used to extract the filtered data to. In this example I would only like to see the data from the Code and Product columns in my filter results.

The criteria range can be placed in any open cells on your worksheet and you only need to copy the column labels of the columns you will filter by.

2. Enter the criteria you want to filter for below the criteria labels
• Criteria entered on the same line must occur in the same record (And logic)
• Criteria entered on separate lines must occur in different records (Or logic)
• Criteria headings with no data under them are not filtered
• Duplicate criteria headings allow more than one set of criteria to be entered i.e. for a date column you may use less than and specific date and greater than a specific date under separate criteria headings


3. Click Data > Filter and then Advanced Filter from the submenu
The Advanced Filter dialogue box appears


4. Select either the Filter the list, in-place (like Autofilter) or Copy to another location option button
5. In List range, select the range of data to be filtered
6. In Criteria range, select the criteria range
7. If copying the filter results to another location, enter the range you wish to copy to in the Copy to field


8. Click Ok

Working with Formula Errors in Microsoft Excel 2003

The Error Checking feature of Excel helps us to deal with formula errors. A formula error appears whenever a formula cannot function and complete its task.

Error Checking

Error checking can be performed on an individual cell or on the entire worksheet.
To error check an individual cell:

1. Select the cell containing the formula error
2. Point to the smart tag icon that appears in the corner of the cell
A tip appears explaining why you are getting this type of error. Click on the smart tag to display a list of error checking options.

To error check the worksheet:

1. Click the Error Checking button on the Formula Auditing toolbar

Excel selects the first cell on the worksheet containing an error and displays the Error Checking dialogue box. The formula is shown along with an explanation of why you are getting this type of error.

The dialogue box contains various error checking buttons.

• Help on this error: Displays a help topic explaining the type of error you are seeing
• Show Calculation Steps: Displays the Evaluate Formula dialogue box breaking down the formula arguments to isolate the error. Click Step In to examine the source of the particular argument
To evaluate a formula, you can also click the Evaluate Formula button on the Formula Auditing toolbar.

• Ignore Error: Skip this error and moves onto the next one in the worksheet
• Edit in Formula Bar: Places the cursor in the Formula Bar where you can directly edit the formula

3. Click the button you want to use to fix the formula error

Click the Options button in the Error Checking dialogue box to change the error checking rules.

Excel errors

Error	Reason
#####	The numeric value is too wide to display in the cell. Resize the column width to fix. This error also appears for negative dates
#VALUE!	The formula references a text entry instead of a numerical entry
#DIV/0!	The formula is trying to divide by 0 or a blank cell
#NAME?	The formula contains text that it does not recognise. Commonly displayed because a function name or range name has been misspelt. Also if a text entry is not enclosed in double quotation marks
#N/A	The formula references a value that is not available to it yet. Commonly seen in Lookup and reference functions such as Vlookup
#REF!	A cell reference is not valid. A cell range referenced within the formula has probably been deleted
#NUM!	The function contains an invalid argument
#NULL!	An intersection of two cell ranges has been specified that do not intersect

Tracing Formulas in Excel 2003

When errors occur in worksheet formulas it can be difficult to diagnose where the problem lies. It is not always the cell directly referenced by the formula causing the problem. There are a number of auditing tools provided by Excel to assist you in identifying the cause of a formula error.

Displaying formulas

Excel displays the result of formulas by default. It can be useful when tracing errors to be able to see all the formulas on a worksheet, so you can see how they are put together.

1. Click Tools > Options
2. Click the View tab in the Options dialogue box
3. Check the Formulas checkbox in the Window options panel
Formulas are shown in the worksheet and the columns widen where necessary to accomodate them.

When the formulas are displayed, select a cell containing a formula to see coloured borders appear around any cells referenced by the formula.

Uncheck the Formulas checkbox in Options to hide the formulas.

Tracing precedents and dependents

Tracing precedents and dependents show you what other cells are affected by or are affecting a certain cell. Arrows are displayed to show the precedent and dependent cells.

1. Select the cell containing the formula you want to trace
2. Click either the Trace Precedents or Trace Dependents button on the Formula Auditing toolbar

Trace Precedents: Displays arrows showing the cells affecting the selected cell.
Trace Dependents: Displays arrows showing the cells being affected by the selected cell.
Arrows appear showing cells related to the formula in the selected cell. Dots appear on the arrows to identify the specific cells. An icon is used to indicate precedents or dependents on another worksheet.
The image below is displaying the dependents of cell D11.

To remove arrows from the worksheet:

• >Click either the Remove Precedent Arrows, Remove Dependent Arrows or Remove All Arrows button on the Formula Auditing toolbar, or
The relevant tracing arrows disappear.

Using the Watch Window

The Watch Window allows you to monitor the values of certain cells as changes are made to worksheets. This can include cells from different worksheets and workbooks.

1. Click the Show Watch Window button on the Formula Auditing toolbar

The Watch Window appears ready for you to add the cells you want to watch.

2. Click the Add Watch button
3. Select the cell or cell range you want to watch and click Ok
The cells are added to the Watch Window.

When a cell value is changed on the worksheet, the Watch Window displays how it affects the cells in the other worksheet or workbook.
If you no longer need to track a certain cell. Select the cell in the Watch Window and click the Delete Watch button.

Data Validation in Excel 2003

Data Validation allows you to specify what data is valid for a range of cells, helping to prevent errors on data entry. Data Validation also enables the creation of messages to assist the user when entering data and warn them of incorrect enries.

Creating a list of options

Using Data Validation you can create lists of items for the user to select from. This will prevent spelling mistakes, typos and usually speed up data entry.

1. Select the range of cells you want to validate
2. Select Data Validation from the Data menu
3. The Data Validation dialogue box appears.
Click the drop list arrow for the Allow field and select List


4. In the Source field, either type the entries for the list seperated by commas, or select the range of cells holding the required entries
Ensure the In-cell dropdown checkbox is checked so that the list appears as a drop down menu

5. Click Ok

Data Validation is applied to the selected cells.
Now when the user clicks on a cell to enter data, the list of entries will appear.

If the user types an entry into a validated cell that does not match an entry in the list, the Data Validation error message appears

Excel messages are not known for their user friendly, easy to understand explanations, and this message is no different. However it is possible to create your own messages.

Data Validation messages

You can create your own input message (message that appears when a user selects a cell for data entry), and error alert (message that appears when a user enters invalid data.

1. Select the range of cells with the Data Validation applied
2. Click the Data menu and select Data Validation
3. Click the Input Message tab in the Data Validation dialogue box
Enter a Title and Input Message

4. Click the Error Alert tab
Select a Style, Title and enter an Error message.
Ensure the Show error alert after invalid data is entered check box is checked so that data entries are validated. This can be unchecked if you want the functionality of the list, but would like users to be able to enter items that are not in the list


5. Click Ok

Now when a cell is selected or invalid data entered, the Data Validation messages will appear providing assistance to the user. The image below shows both messages displayed.

Validating dates and number entries

Validation can be applied to cells to help prevent the entry of incorrect number and date values.

1. Select the range of cells you want to validate
2. Click the Data menu and select Data Validation
3. Click the drop list arrow for the Allow field. Options are provided for validating whole numbers, decimals and dates
Select the required option from the list

4. Select an operator from the Data list
5. Complete the necessary fields by entering the value or refering to a cell or range of cells that hold the validation criteria
The example below is validating entries to not accept a date in the past


6. Click Ok

Các hàm, công thức thường dùng trong Microsoft Excel

Sau đây là tổng hợp các hàm dùng phổ biến nhất trong excel. Các hàm dưới đây sẽ rất cần thiết đối với những người phải làm việc nhiều với bảng tính excel. Với mỗi hàm có ví dụ cụ thể để giúp người đọc dễ hình dung hơn.

1. AVERAGE là hàm cho biết kết quả trung bình cộng của các số
VD: =AVERAGE(x1,x2)

2. ROUND cho kết quả làm số tròn đến m chũ số thập phân
VD = ROUND = (5.1314,2) sẽ cho kết quả là 5.13
VD = ROUND = (5.1354,2) sẽ cho kết quả là 5.14
VD = ROUND = (5.1354,1) sẽ cho kết quả là 5.1
VD = ROUND = (5.1354,0) sẽ cho kết quả là 5
VD = ROUND = (121896,-2)sẽ cho kết quả 121900

3. MIN cho kết quả là số nhỏ nhất trong các số
VD =MIN(2,3,4,10,25) sẽ cho kết quả là 2

4. MAX cho bết kết quả là số lớn nhất trong các số
VD = MAX(2,3,4,10,25) sẽ cho kết quả là 25

5. SUM cho biết két quả của các số trong ngoặc
VD = SUM(2,4,6)sẻ cho kết quả là 9

6. UPPER trả về chỗi chữ hoa của chỗi text
VD = UPPER(“Thanh Da”) sẽ là “THANH DA”

7. PROPER là hàm trả về một chuỗi text và cho ký tự đầu của mỗi từ là chữ hoa
VD = PROPER(“thanh đa”)sẽ cho là Thanh Đa

8. RANK là hàm xếp hạng tăng hoặc giảm dần
VD = (H3,$H$3:$H$45,0) sẽ xếp hạng người có diểm trung bình cao nhất sẽ là 1
VD = (H3,$H$3:$H$45,1) sẽ xếp hạng người có điểm trung bình thấp nhất sẽ là 1

9. HLOOKUP (trị dò ,bảng,hàm tham chiếu,cách dò)
nguyên tắc hoạt động giống như VLOOKUP khác là
VLOOKUP thì dò tìm ở cột trái tham chiếu số liệu ở các cột bên phải
HLOOKUP thì dò tìm ở hàng trên cùng và tham chiếu số liệu ở các hàng bên dưới

10. VLOOKUP là hàm tìm trị dò ,bảng ,cột tham chiếu,cách dò)
VD = VLOOKUP(A001,Bảng,2,0) Dò A001 trong bảng ,lấy giá trị ở cột 2

11. MOD (số bị chia ,số chia )cho kết quả là số dư của phét chia nguyên .kết quả có cùng dấu với chia
VD = MOD(12,3)cho kết quả là 0
VD =MOD(9,2) cho kết quả là 1

12. COUNT đếm số phân tử kiểu số
VD = COUNT=(B3 :B7)sẽ đếm từ ô B3 đến ô B7 có giá trị số

13. COUNTA đếm số phần tử khác trống (cả số & chuổi)
VD = COUNTA(B3:B7)sẽ đếm từ ô B3 đến ô B7 có giá trị số hoặc chuỗi (trừ các ô có khoảng trắng)

14. COUNTIF đếm số phân tử trong dãy thỏa điều kiện
VD = COUNTIF(Ả:F3,">=5"sẽ đếm từ Ô Ả đến ô F3 thỏa điều kiện có giá trị lớn hơn hoăc bằng 5
VD = COUNTIF(C1:C45,"Đậu")sẽ đếm từ Ô C1 đến C45 Thỏa điều kiện "Đậu"

15. IF điều kiện ,trị đúng ,trị sai
VD : IF (2<>3,"đúng","Sai")sẽ cho kết quả là đúng
VD : IF (2=3,"Đúng","Sai")sẽ cho kết quả là sai

16. SUMIF (cột chứa điều kiện,,Đ K,Cột tính tổng)tính tổng trên cột của những dòng thỏa điều kiện
VD : SUMIF($A$3:$A$45,"TIVI",$H$3:$H$45)

17. AND (điều kiện 1,điều kiện 2,điều kiện 3,..)
Chỉ cho kết quả đúng khi tất cả các điều kiện đúng (Cho kết quả sai khi chỉ cần một điều kiện sai)

18. OR (điều kiện 1,điều kiện 2,điều kiện 3,..)
cho kết quả sai khi tất cả các điều kiện sai

19. LEFT (Đĩa chỉ ,n)
Lấy n kí tự tù bên trái đĩa chỉ
VD : =LEFT(ThanhDa,5) sẽ là Thanh
VD : =LEFT(Cao11,2)sẽ là Ca

20. RIGHT (Đĩa chỉ ,n)
Lấy n kí tự tù bên phải đĩa chỉ
VD :=RIGHT(ThanhDa,2)sẽ là Da
VD :=RIGHT(CAO11,3)sẽ là 001

21. MID (Đĩa chỉ ,n,m)
Lấy từ ký tự thứ n lấy m ký tự của đĩa chỉ
VD : =MID(ThanhDa,6,2)sẽ là Da
VD : = LEFT(Cao11BT,3,3)sẽ là 001

22. INT (Số X) cho kết quả là phần nguyên của số X
VD := INT(3.1415)sẽ cho kết quả là 3

23. ABS (Số X) cho kết quả là trị tuyệt đối của X
VD: = ABS(-9)sẽ cho kêt quả là 9

24. LEN Trả về chiều dài của chuỗi text
VD:= LEN("THANH DA") sẽ là 8

25. LOWER trả về chuỗi chữ thường của chuỗi text
VD : =LOWER("THANH DA")sẽ là "thanh da"

26. TRIM loại bỏ các khoảng trắng ở trước ,sau và trong chuỗi text
VD : =TRIM(" THANH DA "sẽ là "THANHDA"

27. MATCH (trị dò,bảng 1 chiều,1/0)
hàm trả về giá trị kiểu số và số thứ tự của phần tự này trong mảng,nếu không tìm thấy sẽ trả về giá trị #N/A

28. INDEX (Mảng hai chiều ,số dòng,số cột)hàm trả về giá trị của một ô trong bảng đó là giao của dong và cột
VD :=INDEX(Mảng,2,4)kết quả sẽ là 98

29. TEXT (Giá trị số,định dạnh)
chuyển giá trị số thành chuỗi
VD :=TEXT(12345,12345)sẽ biến số 12345 thành chuỗi 12345
VD := TEXT(456,45)sẽ biến số 456 thành chuỗi 45

30. VALUE (ô chứa số dạng chuỗi)
chuyển giá trị số dạng chỗi thành số
VD :=VALUE(12345)biến chuỗi 12345 thành số 12345

31. CHOOSE (biểu thức,Danh sách)
cho kết quả là một vị trí trong danh sách tương ứng với giá trị biểu thức
VD := CHOOSE(2,"trâm",'nhật","phương")sẽ cho kết quả là nhật

32. NOT (Điều Kiện)
Cho kết quả ngược lại với ĐK
VD = NOT(2<>4)sẽ cho kết quả là FALSE (Sai)

33. SUMIF (rang,đk)Tổng các số thảo Điểu kiện
nếu A1=10,A2=7 VD:=SUMIF(A1:A2,">4")=17

34. SQRT cho kết quả là căn bậc hai của biểu thức số với điều kiện biểu thức số lớn hơn hoặc bằng không
VD = SQRT(16)cho kết quả là 4

35. PI hàm PI không có đối số cho kết quả là gái trị của số PI
VD = PI()cho kết quả là 3,141593

36. TRUNC bỏ các giá trị sau dấu chấm thật phân
VD = TRUNC(123,56) cho kết quả là 123