Data Analytics Full Course 2024 | Part 04 | Advance Excel

1. Conditional Formatting

Conditional Formatting in Google Sheets allows you to apply specific formatting to cells that meet certain criteria. This can help highlight important data, such as values above a certain threshold or dates within a specific range.

To apply conditional formatting in Google Sheets:

1. Select the cells you want to format.
2. Go to Format > Conditional formatting.
3. Set the conditions under the “Format cells if” dropdown.
4. Choose the formatting style.
5. Click Done.

2. Sorting

Sorting helps organize your data in ascending or descending order based on the values in one or more columns. This makes it easier to analyze and find information within your dataset.

To sort data in Google Sheets:

1. Select the range of cells you want to sort.
2. Go to Data > Sort range by column (A-Z) or Sort range by column (Z-A).
3. Alternatively, use Data > Sort range > Advanced range sorting options to sort by multiple columns.

3. Filter

Filters let you display only the rows that meet certain criteria, hiding the others. This is useful for focusing on specific subsets of your data without deleting any information.

To create a filter in Google Sheets:

1. Select the range of cells you want to filter.
2. Go to Data > Create a filter.
3. Click the filter icon in the column header to set filter conditions.

4. Alignment

Alignment controls the horizontal and vertical positioning of text within a cell. Options include left, center, right, top, middle, and bottom alignment, ensuring your data is presented clearly.

To align text in Google Sheets:

1. Select the cells you want to align.
2. Go to Format > Align.
3. Choose from Left, Center, or Right.

5. Text Rotation

Text Rotation allows you to rotate text within a cell to a specific angle. This is useful for fitting long text labels in narrow columns or for adding a unique style to your data presentation.

To rotate text in Google Sheets:

1. Select the cells with text you want to rotate.
2. Go to Format > Text rotation.
3. Choose the rotation style or set a custom angle.

6. Wrapping

Text Wrapping ensures that text that is too long to fit in a cell is displayed on multiple lines within the same cell. This helps keep your spreadsheet tidy and readable without truncating information.

To wrap text in Google Sheets:

1. Select the cells where you want to wrap text.
2. Go to Format > Text wrapping.
3. Choose Wrap.

7. Charts

Charts are graphical representations of your data, such as bar charts, line charts, and pie charts. They help visualize trends, patterns, and comparisons in your data, making it easier to interpret.

To create a chart in Google Sheets:

1. Select the data range you want to chart.
2. Go to Insert > Chart.
3. Customize the chart in the Chart editor that appears on the right.

8. Borders

Borders add lines around cells or ranges of cells to visually separate different sections of your spreadsheet. This helps to organize and highlight important data areas.

To add borders in Google Sheets:

1. Select the cells you want to add borders to.
2. Click the Borders icon in the toolbar.
3. Choose the border style you want.

9. Data Cleaning

Data Cleaning involves removing or correcting inaccurate, incomplete, or inconsistent data from your spreadsheet. Functions like TRIM, CLEAN, and Remove Duplicates are commonly used for this purpose.

To clean data in Google Sheets:

1. Use functions like TRIM to remove extra spaces.
   • Example: =TRIM(A1)
2. Use Find & Replace to correct errors.
3. Use Data > Data cleanup > Remove duplicates to eliminate duplicate entries.

10. Find & Replace

Find & Replace allows you to search for specific text within your spreadsheet and replace it with different text. This is useful for quickly updating or correcting information across large datasets.

To find and replace text in Google Sheets:

1. Press Ctrl + H or go to Edit > Find and replace.
2. Enter the text you want to find and the replacement text.
3. Click Replace all or Replace.

11. Spelling Check

Spelling Check in Google Sheets helps identify and correct spelling errors in your document. It highlights misspelled words and provides suggestions for corrections.

To check spelling in Google Sheets:

1. Go to Tools > Spelling > Spell check.
2. Google Sheets will highlight spelling errors and suggest corrections.

12. IMPORTRANGE

To use IMPORTRANGE in Google Sheets:

IMPORTRANGE is a function that lets you import data from one Google Sheets document into another. By specifying the URL and range, you can consolidate data from multiple sheets into a single location.

1. Use the formula =IMPORTRANGE(spreadsheet_url, range_string).
   • Example: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd1234", "Sheet1!A1:D10")
2. Grant permission to access the other spreadsheet if prompted.

Example Usage and Instructions:

Example 1: Conditional Formatting

You want to highlight cells in column A that contain values greater than 50.

1. Select column A.
2. Go to Format > Conditional formatting.
3. Set the condition to Greater than and enter 50.
4. Choose a formatting style, such as a background color.
5. Click Done.

Example 2: Sorting

You have a list of names in column A and corresponding scores in column B. You want to sort the list by scores in descending order.

1. Select the range A1.
2. Go to Data > Sort range by column B, Z → A.

Example 3: Filter

You want to filter a list of products to show only those that are in stock.

1. Select the range A1.
2. Go to Data > Create a filter.
3. Click the filter icon in the “Stock” column and select only “In Stock”.

Example 4: IMPORTRANGE

You want to import data from another Google Sheets document.

1. Use the formula =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd1234", "Sheet1!A1:D10").

By following these steps and using these examples, you can effectively utilize these features in Google Sheets to manage and analyze your data efficiently.