Basic Excel training KTHFS Amadeus Wennstrm Anders Bergvall

Basic Excel training KTHFS Amadeus Wennström Anders Bergvall 2013 -05 -02

Agenda § Why learn Excel § Basics § § § Working in excel How to move in excel Cells The Ribbon and some effective short commands Formulas for text Freezing cells in formulas Case Find & Replace Sort and auto filter Formula reference to other sheets Error handling

Why learn Excel § Microsoft's Excel dominates the marketplace and is found on nearly every business computer § By learning Excel now you will get up to speed much faster and feel more secure when starting your new job § A few uses of Excel Number crunching: Perform just about any type of financial analysis you can think of Creating charts and diagrams: Create a wide variety of highly customizable charts Organizing lists: Use the row and column layout to store lists efficiently Accessing other data: Import data from a wide variety of sources Creating graphical dashboards: Summarize a large amount of business information in a concise format Automating complex tasks: Perform a tedious task with a single mouse click with Excel’s macro capabilities

Working in Excel § Fomula bar – § Active cell – § Name of cell or range Row headings – § A to XFD (Maximum 16384 columns) Name box – § Market cell Column headings – § A fomula always has to start with an equal sign 1 to 1048576 (Maximum 1048576 rows) Sheet tabs – Split up data for better controll

How to move around in Excel § To speed up your work in Excel use you keyboard instead of mouse – There is a shortcut for everything!!! – Learn them from the begining and you will save tones of time, Google is you friend § Use arrowkeys to move in data – – – – – Ctrl + Arrow key will take you to end of data in choosen direction Shift + Arrow key will activate/ mark cell in choosen direction Ctrl + Shift + Arrow key will activete/ mark cells to end of data in choosen direction Page up will move you one sceen up Page down will move you one sceen down Alt + Page up will move you one screen to the left Alt + Page down will move you one screen to the right Crtl + Space will activate the column Shift + Space will activate the row Crtl + Home will take you to cell A 1 Start the first training in the excel file…

The ribbon and some effective short commands § By pressing Alt you find the short cuts named in the ribbon Short commands Description F 2 Step in and out of activated cell F 4 Lock row, column or both fro cells in fomulas, if not in cell works as redo F 12 Save as Shift F 11 Insert new sheet Alt + o +h+ r Change name of active sheet Ctrl + Page up/ down Move between sheets Ctrl + Home Move to Cell A 1 Ctrl + r Fill formula to the right Ctrl + d Fill formula down Ctrl + Space Activate column Shift + Space Activate row Ctrl + plus sign(+) Add Cell/ Row/ Column Ctrl + minus sign (-) Delete Cell/ Row/ Column

Cells § Format cells by pressing Ctrl + 1 § § Change the type of number format depending on data E. g. Date, currency, percentage Change colour of text and cell Set a border to the cell or cells Change protection to the cell to be able to write in it after protecting the whole sheet or workbook with password Make a new row in a cell by pressing Alt + Enter Make a bullet in a cell by pressing Alt + 7 or 9 on the numpad

Formulas for text § § § Problems in data are often one of the most time consuming tasks Can often be solved with the right knowledge of formulas to get the data in the format that you need Use text to data if you want to split up text, data that you want to use often comes as a text file and not in nice excel format Formulas often used for text Formula Description RIGHT(text, num_chars) Text is the text string containing the characters you want to extract and specifies the number of characters you want RIGHT to extract. Takes text from right LEFT(text, num_chars) Takes text from left LEN(text) Count number characters in text Search( text you search for, text to search in, start_position ) Find the number of a character TRIM(text) Removes all spaces from text except for single spaces between words Mid(text, start [, length ] ) Returns a variant containing a specified number of characters from a text & Used to put text together from diffrent cells D 3&D 5 ”text” Used to write text in a formula Value(text) Convert text to value Start the second training in the excel file…

The case Functions § Find & Replace § Sort § Auto filter § Paste special § Function references to other sheets § STDEV, AVERAGE & MIN/MAX Instructions § Change dots to comma to be able to work with the numbers § Add three new sheets and name them after each company § Use sort and autofilter function to separate the different companies § Add the separated company data to the corresponding sheet § Go to the sheet "Sort data results" § ill in the table by using STDEV, AVERAGE & MIN/MAX functions with references to the company sheets Start the case training in the excel file…