Please enable JavaScript.

Coggle requires JavaScript to display documents.

Excel Intermediate I - Coggle Diagram

- - - - Click on the cel
        
        Change its name using the name box next to the formular bar
      - Formulas tab > Defined names group > Define name
        
        Beware of the scope
    - - Click on the first data in the column
        
        Ctrl + shift + down arrow to select all the data in the column
        
        Rename using the name box
      - Formulas tab > Defined names group > Define name
        
        Beware of the scope
    - - Select the data (including the headings)
        
        Formulas tab > Defined names > Create from selection
  - - - Apply names in the same worksheet as the named range
- - - - Count
    - - Counta
    - - Countblank
  - - - Insert tab > Sparklines
  - - - Click > Chart tools tab > Design tab > Data group > Select data
        
        Left side = data series to plot (vertical axis)
        
        Right side = horizontal axis labels
        
        Top = data selection to chart
      - Click > Chart tools tab > Design tab > Data group > Switch row/column
    - - To chart data series on different axis when dealing with different units of measure in the same chart
        
        Click > Chart tools tab > Design tab > Type group > Change chart type > Combo
  - - - Adjust the vertical axis of the chart
        
        Right click on the axis > Format axis > Axis options > Bounds
      - Add a trendline
        
        Click on the series on the chart > Plus icon > Trendline > More options > Trendline options
        
        Click on the series on the chart > Plus icon > Trendline > More options > Forecast > Display R-squared value on chart
        
        To display R² and see which trendline option is the best fit
    - - Click on the series on the chart > Plus icon > Trendline > More options > Forecast > Forward
- - - - Concatenate
      - Concat (best)
    - - Ampersand sign &
  - - - Proper
      - Lower
      - Upper
  - - - Mid
      - Right
      - Left
  - - - Find
  - - - Function
        
        Today
        
        Current date
        
        Now
        
        Current date and time
    - - In days
        
        Formula
        
        -
      - In years
        
        Function
        
        Yearfrac
    - - Formula
        
        +
        
        Resulting day
- - - - Click anywhere in the data > Insert tab > Tables group > Table (Ctrl + T)
        
        Contextual ribbon tab appears "Table tools"
        
        Give the table a name
        
        Table tools tab > Design tab > Properties group > Table name
        
        Change the style options
        
        Change the style
  - - - Little black arrow appears when hovering over the first cell of the row/column
    - - Apply conditional formatting to highlight duplicate values
      - Tables tools > Design > Tools > Remove duplicates
  - - - Operate filters
    - - Operate filtes
    - - Turn the filter buttons on/off
    - - Total row computes based on the filter
        
        Not the case when using ranges instead of a table
  - - - Create a new row/entry
        
        Incremential data
        
        Fill handle
        
        Enter today's date as fixed value
        
        Ctrl + ;
        
        How
        
        New row : Ctrl + +
        
        Go to the bottom of the table and use the double-headed arrow and drag
        
        Right click > Insert
      - Create new column
        
        Structured references for tables with @
        
        Add the correct number format
        
        Home > Number
        
        How
        
        Right click > Insert
        
        Go to the bottom of the table and use the double-headed arrow and drag
        
        Simply type in the header in the next column
    - - Example
        
        Adding marketing department to the department table
        
        Create a new row for marketing : click the last cell of the table > Press tab
        
        Just type marketing in the department column
        
        The numbers are automatically updated
  - - - Start by sorting the data by the variable you want to make subtotals
        
        Disable all table style options except header row and filter button
        
        Table tools > Design > Tools > Convert to range
        
        Data > Outline > Subtotal
- - - - Right click
      - Hold ctrl + drag
    - - Right click
        
        Tab color
    - - Click on the first sheet
        
        Hold the shift key down
        
        Click on the last sheet you want to include
  - - - Caution
        
        The order of sheets matter since we use ranges in the 3D functions
        
        Structure of the worksheets must be the same
      - Example
        
        Sum the C7 cells of all the worksheets from sheet 1 to sheet 4
        
        To select multiple worksheets, use the same trick as grouping sheets
    - - By reference (allows slightly different structures)
        
        Click on the cell where you want the consolidation to begin
        
        Then data tab > forecast group > data tools > consolidate
        
        Select the data AND the labels
        
        Check the "Use labels in" box(es)
      - By position (must have identical structure)
        
        Click on the cell where you want the consolidation to begin
        
        Then data tab > forecast group > data tools > consolidate
  - - - Double click home tab to minimize it and save space
        
        Ctrl + O to open the other WB
        
        Use the "Arrange all" function
        
        View tab
        
        Tell me what to do menu (Alt+Q)
    - - Display all the workbooks on the same screen
        
        Use the function you want
        
        Beware of the absolute cell references
        
        Press F4 button 3 times to remove absolute cell references
    - - Edit links from queries and connections group of the data tab of the ribbon
        
        Change source of links
        
        Break link
- - - - Create
        
        First, make your data into a table with a name
        
        Click the data > Insert > Tables > PivotTable
        
        Click the cell where you want the pivot table to start > Insert > Tables > PivotTable
      - Update the pivot table after making changes in the original data
        
        PivotTable Tools > Analyze > Data > Refresh
      - Value field settings
        
        Change the values
        
        Right click a value > Show values as
        
        Right click a value > Summarize values by
        
        Subtotals
        
        Create
        
        Put 2 variables into the rows/colums of the pivot table
        
        Change layout of the subtotals
        
        PivotTable tools > Design > Layout
      - Formatting
        
        PivotTable Tools > Design > PivotTable Styles
      - Grouping, sorting anf filtering FIELDS (not the data)
        
        Grouping (dates/numbers fields)
        
        Rearrange how the fields are grouped
        
        Click one of the field > PT Tools > Analyze > Group > Group field
        
        Click one of the field > PT Tools > Analyze > Group > Ungroup
        
        Collapse/expand groups
        
        Click one of the field > PT Tools > Analyze > Active field
        
        Sorting entries/rows
        
        Click one of the values in the column you want to sort by > Data > Sort & Filter > Sort smallest to largest icon
        
        Filter (either by column or row)
        
        Create
        
        Simply click the column/row labels drop down menu
        
        Clear filters
        
        All of the filters
        
        Click on the PT > PT Tools > Analyze > Actions > Clear > Clear filters
        
        Click on the PT > Data > Sort & Filter > Clear
        
        One of the filters
        
        Use the drop down menu on the field
      - Reporting filter pages
        
        Filter the DATA INCLUDED in the PT
        
        Use filters area of the PT Fields Tool
        
        Use the newly appeared report filter
        
        Multiple items means the values displayed take into account multiple items from the variable
        
        Create multiple PT with different filters applied to avoid constantly changing filters/sorts...
        
        Use the report filter to select the different criterias
        
        PT Tools > Anallyze > PT > Options drop down menu > Show report filter pages
  - - - Click > PT Tools > Analyze > Tools > Pivot Charts
    - - Using pivot chart field settings
      - Using switch row/column button
        
        Click > PT Tools > Design > Data > Switch row/column
    - - Click > PT Tools > Analyze > Hide/show
  - - - Click PT > PT Tools > Analyze > Filters > Insert slicer
    - - Arrange the slicers on the screen
        
        Click > Slicer tools > Options > Arrange
      - Resize
        
        Click > Slicer tools > Options > Size
    - - Click > Slicer tools > Options > Slicer styles
    - - Click > Slicer tools > Options > Slicer connections