Scribd
Upload
4 views2 pages

Module 4 Summary

The document outlines Excel's data importation capabilities, including support for .txt and .csv files, and the use of delimiters for data separation. It also discusses text manipulation techniques such as Text-to-Columns, extraction functions, and data conversion methods like case conversion and merging text. Additionally, it highlights the functionality of hyperlinks for navigation within workbooks and provides a quick reference for essential Excel functions.

Uploaded by

acubiam
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Module 4 Summary

The document outlines Excel's data importation capabilities, including support for .txt and .csv files, and the use of delimiters for data separation. It also discusses text manipulation techniques such as Text-to-Columns, extraction functions, and data conversion methods like case conversion and merging text. Additionally, it highlights the functionality of hyperlinks for navigation within workbooks and provides a quick reference for essential Excel functions.

Uploaded by

acubiam
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

Data Importation
Excel allows users to bring in large datasets from external files using the Get & Transform Data
feature (built on Power Query technology).

●​ .txt Files: Plain-text files often using Tabs as delimiters to separate data.​

●​ .csv Files: "Comma Separated Values" files, which are the industry standard for exporting
data from websites and databases.​

●​ Delimiters: Characters like commas, tabs, semicolons, or spaces that tell Excel how to
split data into separate columns during the import process.​

2. Manipulating and Extracting Text


Once data is imported, it often needs to be split or refined.

●​ Text-to-Columns: A wizard-based tool used to split a single column of data (like a full
name) into multiple columns.​

●​ Extraction Functions:
○​ LEFT / RIGHT: Extracts a specific number of characters from either the start or end
of a string.​

○​ MID: Extracts characters from the middle of a string based on a specified starting
position.​

○​ LEN: Counts the total number of characters in a cell, including spaces.​

3. Converting and Combining Data


The module covers standardizing text format and merging information from different cells.

●​ Case Conversion:
○​ PROPER: Capitalizes the first letter of each word (e.g., "john smith" to "John Smith").​

○​ UPPER / LOWER: Converts all text to all caps or all lowercase.​

●​ Paste Special (Values): A critical step used after applying case functions to replace
formulas with static text, allowing the original "uncleaned" data to be deleted without
causing errors.​

●​ Merging Text:
○​ CONCAT: Joins text from multiple cells; it replaces the older CONCATENATE
function.​

○​ TEXTJOIN: A more advanced tool that allows for a repeating delimiter (like a comma)
and can automatically skip empty cells.​

4. Hyperlinks and Navigation


To improve workbook usability, Excel supports various clickable links:

●​ Internal Links: Navigate to specific cells, worksheets, or Named Ranges (descriptive


names given to groups of cells).​

●​ External Links: Connect to websites, other files (PDFs, Word docs), or pre-fill email
addresses using the mailto: prefix.​

●​ Management: Hyperlinks can be removed or edited via the right-click context menu or
the "Edit Hyperlink" dialog.​

Quick Function Reference

Function Purpose Example Result

LEFT Extract from beginning


=LEFT("316-555", 3) → "316"

PROPER Capitalize names


=PROPER("excel") → "Excel"

CONCAT Join cells


=CONCAT("A", "B") → "AB"

LEN Count characters


=LEN("Data") → 4

You might also like