Go To Special

 Go To Special

The "Go To Special" feature in Excel allows users to quickly select specific types of cells, such as blanks, constants, or formulas, for efficient data manipulation. It enhances productivity by streamlining tasks like data cleaning, troubleshooting, and analysis.

1. Blanks

  • Function: Selects all blank cells in the range.

  • Process Flow:

    1. Select the range of cells (e.g., A2:D6).
    2. Navigate to the Home tab, click on Find & Select.
    3. Choose Go To Special from the dropdown menu.
    4. In the dialog box, select Blanks and click OK.
    5. Action: All blank cells in the selected range will be highlighted.

  • Example:


    Selecting Go To Special > Blanks highlights cells C2, B3, D4, and A5.

  • Next Step: Fill these blank cells with a value like N/A by typing N/A and pressing Ctrl + Enter.

2. Constants

  • Function: Selects all cells containing constant values (numbers, text, dates).

  • Process Flow:

    1. Select the range of cells (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. In the dialog box, select Constants and click OK.
    4. Action: All cells containing constant values will be highlighted.

  • Example:



    Selecting Go To Special > Constants highlights cells with constant values: A2, B2, C2, D2, A3, B3, A4, B4, C4, D4.

  • Next Step: You can update or apply formatting to these constant values.

3. Formulas

  • Function: Selects cells containing formulas.

  • Process Flow:

    1. Select the range of cells (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. In the dialog box, select Formulas and click OK.
    4. Action: All cells containing formulas will be highlighted.

  • Example:



    Selecting Go To Special > Formulas will highlight cells B2, D2, B3, B4 as they contain formulas.

  • Next Step: You can adjust or copy the formulas in these cells.

4. Row Differences

  • Function: Selects cells that differ from the first cell in the row.

  • Process Flow:

    1. Select the range of cells (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. In the dialog box, choose Row Differences and click OK.
    4. Action: Cells in each row that differ from the first cell in the row will be highlighted.

  • Example:



    Using Go To Special > Row Differences will highlight B2, C2, D2, C3, D3 as they differ from the first cell in the row.

  • Next Step: You can apply changes to these differing values.

5. Column Differences

  • Function: Selects cells that differ from the first cell in each column.

  • Process Flow:

    1. Select the range of cells (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. In the dialog box, select Column Differences and click OK.
    4. Action: Cells that differ from the first cell in the column will be highlighted.

  • Example:



    Selecting Go To Special > Column Differences will highlight B1, C1, D1 as they differ from column A.

  • Next Step: You can modify these cells as needed.

6. Precedents

  • Function: Selects all cells that are referenced by formulas in the selected range.

  • Process Flow:

    1. Select the cell (e.g., B7).
    2. Go to Home > Find & Select > Go To Special.
    3. Choose Precedents and click OK.
    4. Action: Cells referenced by the formula in B7 will be highlighted.

  • Example:




    Selecting Go To Special > Precedents will highlight A2, A3 because they are referenced in the formula in B4 (=SUM(A2:A3)).

  • Next Step: You can check or modify the source data in the referenced cells.

7. Dependents

  • Function: Selects all cells that depend on the selected cell or range.

  • Process Flow:

    1. Select the cell (e.g., B2).
    2. Go to Home > Find & Select > Go To Special.
    3. Choose Dependents and click OK.
    4. Action: Cells that depend on B2 will be highlighted.

  • Example:



    Selecting Go To Special > Dependents will highlight cells like B1, C1 because they depend on A1.

  • Next Step: You can edit or modify formulas in the dependent cells.

8. Conditional Formats

  • Function: Selects all cells that contain conditional formatting.

  • Process Flow:

    1. Select the range (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. Choose Conditional Formats and click OK.
    4. Action: Cells with conditional formatting will be highlighted.

  • Example:

    If you have conditional formatting applied, selecting Go To Special > Conditional Formats will highlight cells based on the format rules.

  • Next Step: You can modify or remove the conditional formatting rules.

9. Objects

  • Function: Selects all objects (shapes, charts, pictures, etc.) in the worksheet.
  • Process Flow:
    1. Go to Home > Find & Select > Go To Special.
    2. Choose Objects and click OK.
    3. Action: All objects in the worksheet will be highlighted.
  • Example: If you have inserted a chart and images, selecting Go To Special > Objects will highlight all the charts and images on the sheet.
  • Next Step: You can move, delete, or format the selected objects.

10. Visible Cells Only

  • Function: Selects only the visible cells in a filtered range.
  • Process Flow:
    1. Select the range (e.g., A2:D6 with a filter applied).
    2. Go to Home > Find & Select > Go To Special.
    3. Choose Visible Cells Only and click OK.
    4. Action: Only the visible cells in the filtered range will be highlighted.
  • Example: If you apply a filter to display rows where Age > 30, selecting Visible Cells Only will highlight only those rows.
  • Next Step: You can copy or format the visible cells.

11. Comments

  • Function: Selects all cells with comments.
  • Process Flow:
    1. Select the range of cells (e.g., A2:D6).
    2. Go to Home > Find & Select > Go To Special.
    3. Choose Comments and click OK.
    4. Action: Cells with comments will be highlighted.
  • Example: If cells B2 and D3 contain comments, selecting Go To Special > Comments will highlight these cells.
  • Next Step: You can modify or delete the comments in the selected cells.

Comments

Post a Comment

Popular posts from this blog

How to Use the FILTER Formula

Image
FILTER function Excel for Microsoft 365   Excel for Microsoft 365 for Mac   Excel for the web   Excel 2021   More... The  FILTER  function allows you to filter a range of data based on criteria you define. In the following example we used the formula  =FILTER(A5:D20,C5:C20=H2,"")  to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string (""). Syntax Examples FILTER used to return multiple criteria In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples  AND  are in the East region:  =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"") . FILTER used to return multiple criteria and sort In this case, we're using the previous FILTER function with the SORT function to return all values in our array range (A5:D20) that have Apples  AND  are in the East region, and then sort Units in descending order:  =SORT(FILTER(A...
Read more

Using Flash Fill in Excel

Image
            Using Flash Fill in Excel Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note:  Flash Fill is only available in Excel 2013 and later.   Enter the full name in cell C2, and press  ENTER . Start typing the next full name in cell C3. Excel will sense the pattern you provide, and show you a preview of the rest of the column filled in with your combined text. To accept the preview, press  ENTER . If Flash Fill doesn't generate the preview, it might not be turned on. You can go to  Data  >  Flash Fill  to run it manually, or press Ctrl+E. To turn Flash Fill on, go to  Tools  >  Options  >  Advanced  >  Editing Options  > check the  Automatically Flash Fill  box.
Read more

Skill Matrix Tool & Excel Format

Image
Skill Matrix Tool About Skill Matrix: A skill matrix tool , also known as a competency matrix or skill assessment matrix, is a valuable resource for organizations and individuals alike. It helps assess and visualize the skill levels and proficiencies of individuals within a team or organization. Here are some benefits of using a skill matrix tool: Identifying Skill Gaps: A skill matrix tool allows you to clearly see where there are gaps in skills within your team or organization. This can help in planning training and development programs to address these gaps and enhance the overall skill set. Training and Development Planning: With a skill matrix in place, you can create targeted training and development plans for individuals. This ensures that employees receive training that is directly relevant to their current skill levels and future career goals. Succession Planning: Skill matrices can assist in identifying potential successors for critical roles within the organization. By un...
Read more