MS Excel – linking spreadsheets

MS Office course designed for employees of the University of South Bohemia. 

Effective linking of data between different tables, sheets, and workbooks, searching for information, and working with text and search functions.

Calculations between sheets and workbooks

• References between sheets: =Sheet2!A1

• References between workbooks: ='[Workbook2.xlsx]Sheet1'!A1

• Practical examples: summary calculations, data consolidation

Text functions for working with data

• LEFT, RIGHT, PART, CONCAT, CLEAN, TRIM

• Data cleaning: removing spaces, format adjustment

• Extracting information from text (e.g., product code, name from email)

• Dynamic filling

• Joining text from multiple cells

Searching between tables

• Search principle: searching for a value in another table

• VLOOKUP function: syntax, use, limitations

=VLOOKUP(lookup_value; table; column_number; [type])

• Practical examples: assigning prices, searching for names by ID, filling in data

• XLOOKUP alternative

Mgr. Vendula Mikolášková
This email address is being protected from spambots. You need JavaScript enabled to view it.