WPS Academy
Top Searches
    Login/Sign up
    Login/Sign up
    • Home
    • Popular
    • fix circular reference in excel

    32 results for fix circular reference in excel

    • All Tutorials
    • Video Tutorials
    • Quick Tutorials
    • All
    • Spreadsheet
    • Presentation
    • Writer
    • PDF

    32 results for fix circular reference in excel

    Why error message NA occurs and how to solve it

    Why error message NA occurs and how to solve it

    02:56

    Why error message NA occurs and how to solve it

    When using the VLOOKUP function to locate data, a #N/A error sometimes will appear.Two common causes are listed as followed. The first reason: the searched value is not in the searching range. Take this table as an example. To get the sales of erasers, black pens, pencils, and blue pens, we need to insert the following formula: =VLOOKUP(F3,A2:D9,4,0) F3 is Lookup_value, Eraser is the value to look for. A2:D9 is Table_array, the entire table is the range containing the data to look for.....

    Spreadsheet views
    The INDEX function

    The INDEX function

    03:50

    The INDEX function

    The INDEX function can look up a value regarding its row and column numbers.There are two forms of the INDEX function, the Array form and the Reference form. · One: Array formWe'll use the Array form to return the value located in the seventh row and the fourth column.First, select cell J6. Next, click Formulas and then Insert Function to insert the INDEX function. The function syntax:INDEX(array,row_num,column_num)The first parameter, 'Array', refers to the array for valu....

    Spreadsheet views
    Use Validation to restrict data change

    Use Validation to restrict data change

    02:24

    Use Validation to restrict data change

    We want to lock the cells after checking the table data and prevent it from being further changed. What can we do?In today's tutorial, you will learn how to use the Validation function to restrict data change.Step one, select the E column, and then add a drop-down list named checked.Click Validation at Data, and you can also use the shortcut Alt+D+L.In the Setting tab, select List at Allow, and enter checked at Source. Finally, click OK.By doing so, we can select checked in the dro....

    Spreadsheet views
    Relative reference, absolute reference, and mixed reference

    Relative reference, absolute reference, and mixed reference

    03:46

    Relative reference, absolute reference, and mixed reference

    When copying formulas to other cells, we might encounter three reference modes, namely relative reference, absolute reference, and mixed reference. We need to choose the proper reference method according to the specific calculation requirements and the formula. · 1. Relative ReferenceWhen copying formulas to other cells, the formula will change according to the position of the referenced cell.Take this table as an example. Enter =B2*C2 in cell D2, and then pull down the fill handle to ....

    Spreadsheet views
    How to use the FILTER Function

    How to use the FILTER Function

    03:14

    How to use the FILTER Function

    Lookup and Reference functions are frequently used for processing data in a table. Today we will learn how to use the FILTER Function to filter data.This function can be used to filter a range of data based on the criteria that you specify.· Primary usage of the Filter functionTake this table as an example. Suppose we want to get the list of class 1, Select G3:G15, this is the cell range we want the result to return, so don't just select cell G3.Enter the formula: =FILTER in the ce....

    Spreadsheet views
    Use formula to set the conditional formatting

    Use formula to set the conditional formatting

    01:28

    Use formula to set the conditional formatting

    Take this table as an example. We can use the formula to set the conditional formatting by ourselves and mark the finished projects. Select the cell range A2:C22, and get into the Home tab. Click the Conditional Formatting drop-down button, and choose the New Rule option. In the pop-up dialog, we choose the Use a formula to determine which cells to format option.In the Format only cells with edit box, enter the formula "=$C2="Finished" ". The absolute reference has been added to the co....

    Spreadsheet views
    How to split sheets by content

    How to split sheets by content

    01:16

    How to split sheets by content

    When you want to split the content based on the specific categories in the sheet, are you still disassembling and reorganizing the content one by one? The Split Sheet feature can help you. Take this sheet as an example. If we want to split this sheet into three sheets based on class 1, class 2 and class 3, what should we do? We just need to click the Home tab and click Worksheet, then click the Split Sheet option, finally choose Split Sheets By Content. In the Split area box, we can ch....

    Spreadsheet views
    SUBTOTAL function: Calculate the data with the hidden value

    SUBTOTAL function: Calculate the data with the hidden value

    02:33

    SUBTOTAL function: Calculate the data with the hidden value

    Take this table as an example. When using the SUM function to do summations, we may find two rows hidden in the table, but the result of the summation still includes the hidden value. So how can we calculate the data containing hidden values and get a result that does not include the hidden values? This video can help us and it can be also used in Microsoft Office Excel and Openoffice. In this case, we can use the SUBTOTAL function. Next, we are going to show you the usage of the SUBTO....

    Spreadsheet 0.1546 views
    Prev 1234 Next
    WPS Academy
    Products
    Partners