Tools that are necessary during everyday work in Excel spreadsheet

• Advanced formatting of the spreadsheet

  Custom spreadsheet formatting, essential for the proper use of the sheet in the visual aspect, which improves the work

  • Practical use of text, number and date formats

    Completing the cells with data in a quick way using keyboard shortcuts helpful in formatting

  • Custom formatting of the spreadsheet

    Using the painter format tool, creating custom borders, quick adjustment of row height and column width, making custom formats, such as “m2”, “kg”

  • Using “Go to” tool

    Express filling empty cells using keyboard shortcuts

  • Creating custom data series

    Creating specific data series (recurring data) and the methods of using them, creating dates excluding weekends, creating custom serial numbers

• The spreadsheet modification
  • „Paste Special” - fast data copying and pasting

    Copying only cell values, copying and pasting tables to other worksheets and documents without changing their formats and column width, applying „transpose” to quick conversion rows into columns and the other way round, performing simple arithmetic calculations (multiplication, division, addition, subtraction) with the „Paste Special” tool

  • Data grouping

    Data grouping with the Group tool in order to hide columns and rows quickly and effectively

  • Working with and applying TABLE object and its features

    Creating tables and lists, making databases with the Table tool, performing quick calculations within a table

  • Splitting texts using the „Text to columns” tool

    Rapid division of the text in one cell into multiple columns, splitting texts together with changing their location

  • Using Excel keyboard shortcuts in practice

    Keyboard shortcuts as a way to enter and select data, wrap text lines in cells, to create data, such as today's date rapidly as well as to navigate through the workbook and data

  • Automatic change of worksheet cell appearance (formatting) due to specific conditions – Conditional formatting

    Making “graphic” conditional formats, conditional formats which are used to good visualization of data collected in worksheets: text, number and date format. Conditional formats using simple functions: automatic change of the color of the whole row, automatic selection of rows including weekends.

  • Limitation of possibilities of making mistakes with the “Data validation” tool

    Reducing the risk of entering incorrect data, restrictions related to number and date correction and text length Creating simple drop-down lists

  • Comments and references

    Creating “ graphic comments”, creating simple navigation through the workbook using hiperlinks, opening other files automatically with hiperlinks

Calculations – Using Excel formulas and functions in everyday work with the spreadsheet

• Quick entering and modifying the range of functions: SUM, AVERAGE, MIN, MAX, COUNTA

  Keyboard shortcuts as a method of quick creating and modifying functions, the change of the ranges of functions using keyboard shortcuts

• Absolute and relative references (blocking worksheet cells with $ mark)

  Working with functions and references, performing calculations by blocking cells with keyboard shortcuts

• Logical functions: IF, AND, OR

  Applying IF function to calculate data due to a specific condition, nesting IF function with the AND, OR ones

• Counting functions: SUMIF, COUNTIF, SUMIFS

  Using the functions for summing different ranges due to specific conditions, counting functions including cell blocking, summing the data which meet many criteria

• Using ROUND function

  ROUND function as a way to round numbers, creating nested functions which count the already rounded data

• Selected text functions: LEFT, RIGHT, CONCATENATE, UPPER, LOWER, TEXT, VALUE

  Simple text splitting using functions, separating letters situated at the beginning and the end of the text, joining texts quickly using functions and the ampersand character (&), converting upper case to lower case and the other way round, using TEXT function to find a day of a week and the month, rapid conversion of numbers recognized as text into numbers

• Date and time functions: TODAY, NOW, YEAR, MONTH, DAY

  Adding and subtracting dates, subtraction month and year from a date, addition month and year to a date, operations on dates, such as constructing functions which check whether a particular date has passed

• Simple use of VLOOKUP function

  An easy method for comparing two tables and for completing the values from one table into another one taking into account common criteria

• Working with nested functions

  Creating nested tables using “Insert function”, nesting newly-known functions into other functions, nesting text and date functions into logical functions

• Look-up and error correction in calculations

  Types of errors in calculations, methods of finding and correcting errors, using the”Evaluate formula” tool to verify whether the calculations are correct

• Formula auditing

  Checking formula relations, “graphic” representation of formula dependence using “Trace Dependents” and “Trace Precedents”

Creating relations between Excel sheets

Calculations based on data from other sheets

• Making simple relations

  Creating calculations from data located in other sheets using SUM, AVERAGE and IF functions

• Relations and references through name

  Usefulness of names, name defining, naming cell ranges, creating calculations through name, retrieving data from other sheets in order to do calculations referring to the name

• Retrieving data from other worksheets using VLOOKUP function

  Transferring data from one table to another using VLOOKUP function and reference to name

Data sorting and filters

• Sorting data by column

  Sorting as a method for quick alphabetical ordering of data, sorting data by color, sorting data by date, sorting data according to many criteria

• Simple filters

  Filtering as a way to find data quickly, applying filters in tables, data filtering by many criteria, number, date and text filtering

• Advanced filters

  Filtering data by fragment of text, data filtering using * mark

Creating calculations due to selected data groups using “Subtotal” tool

• Creating subtotals

  Preparing data for the use of subtotal, making subtotals on already sorted data, creating subtotal using Average summary function

• Subtotals by many categories

  Using summary functions: Sum and Average in Subtotal tool based on many criteria

Report and analysis creating with PivotTables

A PivotTable tool as a method to analyze business data, to create calculations rapidly as well as to report data. Exercises on accounting, financial and production data

• Creating a PivotTable

  Preparation of data needed to make a PivotTable, creation of PivotTable, introduction to basic elements of PivotTables, the list of PivotTable fields, discussion on how a PivotTable works

• Working with PivotTables
  • Data transferring

    Adding data to row and column fields as well as to filter and value fields, methods of data transfer so as to get correct data sets

  • Change of PivotTable summary functions

    The use of SUM, AVERAGE, MIN, MAX functions in order to create calculations which meet specific criteria

  • Data grouping in PivotTable

    Change of data presentation due to specific data groups, creating calculations based on months and years, creating computational intervals

  • Data filtering in Pivot Table
• Quick creation of PivotChart using Pivot Table data

Working with charts and graphic elements in Excel

• Chart creating

  Proper data preparation in order to create a chart, choosing the most appropriate chart to present data: column chart, pie chart, line chart and radar chart

• Change of data range in charts

  Changing the range of data in chart, methods of automatic data modification using Table object

• Charts with many data series

  Creating combination charts, making charts with dynamic data, charts comparing variables

• Chart formatting

  Adding graphic elements to the chart, modifying data presentation

• Creating objects such as “SmartArt”

  Making graphic representation of data hierarchy, process, organizational hierarchy schemes and relations between data

Getting external data to Excel and data consolidation from different workbooks

• Importing text and number data

  Methods of importing data from text files and the most common errors appearing in data. Methods of elimination of errors from imported data

• Data consolidation

  Consolidating data with the “Consolidate” tool, data consolidation using SUM and AVERAGE, consolidating data by row and column labels

Protection of Excel worksheets

• Protecting worksheets from unauthorized modifications

  Worksheet protection from changing entirely, worksheet protection allowing column and row deletion

• Workbook protection

  Workbook protection from the possibility of sheet deletion or view change

Printing Excel worksheets

Creating graphic headers and footers in Excel, printing selected sheet areas, printing complex tables