Formation excel
576 mots
3 pages
Excel Training (Basics) 1. Basic Excel functions a. Sort i. If you want to sort by 1 column, you just have to put your cursor in the column you want to use, and click the icon in Data Menu.Example : sort by GL account in below screenshot
ii. When screen appears, only one row “Sort by” is shown, click here to add one level
To organize the order of the filters
This means your 1st row is titles
If you need to sort by several values (ex. : GL accounts, and within each GL, sort by date), you need to use the icon in the Data Menu
b. Conditional Formatting
You can use this to put a special format on certain value in a column for example.
Ex : mark in red amounts >= 10,000 in columns I to L:
c. Autofilters
Filter icon in Data Menu . When you press the icon, arrow appears on every column title that you can use to filter on particular data/colors, ...
d. Subtotals
Used if you want for example to have the total by GL account.
Before to do a subtotal, you need to do a sort on the column you want to use as basis.
e. Paste Special
Copy the info you need. Where you want to paste, right click and select “Paste Special”.
f. Select Visible Cells
Use when you have a filter or hidden rows, to copy only the cells visible on screen.
To add the icon to your excel shortcuts :
Excel options, customize Quick Access Toolbar, choose commands from All, look for ”Select Visible Cells” (alphabetical order).
2. Formula’s
Formulas can refer to a cell / range of cells. If you need to use the same formula in a whole column/row, you might need to lock a reference cell, using the $ sign (press f4). You can lock the whole cell ($A$2), or only the column ($A2) or only the row (A$2). g. Text formula’s iii. Select a part of a text
iv. Group 2 cells together (eventually with additional text)
If you want to use text manually added, put it between “tremas”.