|
Plan and Report
- Learn the indispensable-for-HR, look-up functions.
- Discover a useful function that eliminates monthly formulae editing.
- Create a cell drop-down list of employees.
Import data from other systems
- Keep leading zeros.
- Import dates correctly.
- Split CSV files with Wizard.
- Keep the settings for a repeat next month.
- Append formulae columns.
- Convert numbers to text and vice versa.
Manipulate HR databases
- Complete missing entries.
- Sort by more than three columns.
- Format codes.
- Check for new records.
- Check for missing records.
- Match the data in one table to data in another.
- Locate duplicates.
- Join two tables on employee code.
- View a selection of records with AutoFilter.
- Split first names and last names into seperate columns.
Total database data
- Insert subtotals.
- Learn totalling formulae.
- Discover PivotTables for creating cross-tabulation reports.
- Easily filter data to show records for a specific staff member or department.
- Quickly identify values that fall outside performance guidelines.
|
|
Adopt best practices for workbook and sheet layout
- Discover how to arrange the top, side and sheet titles on every sheet.
- Format quickly with styles.
- Clearly identify inputs, processes and outputs.
- Apply sheet name, colour and flow conventions.
- Discover the six data-sheet types.
- Add the standard Guide and Params sheets to every workbook and understand their benefits.
- Use the free ESPMini add-in to help standardise your worksheets.
Calculate with Date & Time
- Total hours and minutes worked.
- Calculate age accurately.
- List and determine number of working days.
- Multiply time worked by a rate.
Mail-merge with Word
- Make Word access an Excel table of employees.
- Solve formattting problems with merged numbers and dates.
Build organisation charts
- Discover the inbuilt organisation chart feature.
Validate data input
- Restrict data input to valid entries - eg. a salary range.
- Create a drop-down list of cell entries to avoid misspellings.
- Guide users with feedback when they enter invalid data.
The above agenda covers many different functions. They include:
INDEX, SUMIF, COUNTIF, IF, N, VLOOKUP, LEFT, MID,
TRIM, VALUE, SEARCH, NETWORKDAYS, DAY, MONTH, YEAR, TEXT and many more.
It also covers many shortcut tips and tricks that can be used in everyday
spreadsheet work.
|