Manav Goyal

Spreadsheet - Others

Components

  • Top Bar
    • Name
    • Star
    • Move
    • Chat: Lasts only as long as at least one person in the chat ahs the document open
    • Share
  • Menu
    • File
      • New
      • Open (Ctrl + O)
      • Import
      • Version history
        • Name current version
        • See version history
      • Settings
        • Locale
        • Time Zone
      • Publish to web
        • Link
          • Entire Document
          • Web Page, CSV, TSV, PDF
        • Embed
        • Publish/Published
        • Published content & settings
          • Stop publishing
    • Edit
    • View
      • Freeze: Fixed the position
        • No rows
        • 1 row
        • 2 rows
        • No columns
        • 1 column
        • 2 columns
        • Trick
          • Drag and Drop the thick line on Worksheet where it shows hand symbol
    • Insert
      • Chart
      • Image
      • Drawing
      • Form
      • Function
      • Link
      • Checkbox
      • Comment: Visible to others, Can be "Resolved" to remove and send to archives
      • Note (Shift + F2): Add a note to a Cell, Not visible to others
    • Format
      • Number
        • Accounting
        • Financial
        • Currency
      • Conditional formatting
        • Single color
          • Apply to range
          • Format cells if
          • Format style
        • Color scale
        • Tricks
          • Highlight cell => Select only that column
          • Highlight row based on a column => Select all rows & columns
    • Data
    • Tools
    • Extensions
  • Toolbar
    • Format as Percent
    • Decrease decimal places
    • Increase decimal places
    • Font => Default is arial, Others are Consolas, Sorts Mill Goudy
      • More fonts
    • Font size
    • Bold (Ctrl + B)
    • Italic (Ctrl + I)
    • Strikethrough (Alt + Shift + 5)
    • Text color
    • Fill color
    • Borders
    • Merge cells
    • Horizontal align
      • Generally, Numbers are aligned to Right & Text are aligned to Left
    • Vertical align
    • Text wrapping
    • Insert link (Ctrl + K)
    • Insert comment (Ctrl + Alt + M)
    • Insert chart
      • Chart type
        • Column chart
          • Data
            • Stacking
            • Data range
        • Bar chart
          • Tricks
            • To make a particular cell bar of different color, make a grouped bar chart with stacking to 100%
        • Stacked bar chart
        • 100% stacked bar chart
        • Pie chart
          • Customize
            • Pie chart
              • Doughnut hole
              • Slice label
        • Doughnut chart
        • 3D pie chart
        • Histogram chart
          • Customize
            • Histogram
              • Bucket size
              • Outlier percentile
            • Horizontal axis
              • Slant labels
        • Line chart
          • Customize
            • Chart style
              • Smooth
            • Series
              • Point size
            • Horizontal axis
              • Treat label as text
            • Vertical axis
              • Scale factor
          • Tricks
            • Select 2 columns to get a single line
        • Smooth line chart
        • Timeline chart
        • Scatter chart
          • Customize
            • Series
              • Trendline
      • Tricks
        • Put legend in place where it is in direction of the chart
    • Create a filter
    • Filter views
      • Create new filter view
    • Functions
      • SUM, AVERAGE, COUNT, MAX, MIN
  • Worksheet
    • Formula Bar
    • Rows & Columns Bar
      • Click on number/letter to select entire row/column
      • Snap-to-fit => Double click on edge of a row/column or selection of multiple rows/column
      • Move entire row/column by selecting all and dragging and dropping
      • Drop-Down
        • Cut
        • Copy
        • Paste
        • Sort sheet A → Z
          • Trick: Sort a column w.r.t previous column by sorting the latter column first
        • Sort sheet Z → A
    • Cells
      • Right-Click
        • Cut
        • Copy
        • Paste
        • Paste special
          • Paste transposed: Flips from rows to columns and vice versa
        • Insert row
        • Insert comment
        • Insert note
      • Paste formatting => Appears after you paste something
        • Paste values only
        • Paste format only
      • Tricks
        • Use Tab & Enter to move to next cell after entering data
        • Select
          • Ctrl + Space => Select column till end of data (entire row empty)
          • Shift + Space => Select row till end of data (entire column empty)
          • Shift + Ctrl + Space => Select entire data
          • Shift + Arrows => Expand selection
  • Tabs
    • Add Sheet
    • All Sheets
    • Summary
      • Sum
      • Svg
      • Min
      • Max
      • Count

Others

Theory

  • Spreadsheet (Workbook) > Pages (Worksheets/Sheets) > Rows & Columns > Cells
  • Data Types
    • Number: 1234
    • Proportion: 0.12
    • Percentage: 12.00%
    • Decimal: 1.23
    • Date: 7/17/2019
    • Time: 10:31:44
    • Text: Lorem Ipsum
    • Links: http://loremipsum.com
    • Formulas
    • Web Images: =image("URL")
      • Can also insert Local Images (Floating), not attached with cell
    • Web Data: =GOOGLEFINANCE("GOOG")
      • Call in google finance data, Today's stock price for google
  • Tidy Data
    • Columns represents Variables & Rows represents observations
      • Don't use merged cells
    • Try to represent observations in binary format
    • Put repeated data into a separate table
  • Cell References
    • Relative
    • Absolute
      • $ is used
      • $CR or C$R or $C$R
  • Functions/Formula
    • =COUNT(C1R1:C2R2) => Count numbers of numerical values
      • =COUNTA(C1R1:C2R2) => Count numbers of values
    • =SUM(C1R1:C2R2) => Sum
      • =IMSUM(C1R1:C2R2) => Sum of 2 complex number
    • =AVERAGE(C1R1:C2R2) => Mean
    • Rept Charts: Repeat given symbol specified number of times
      • =REPT("|", CR)
    • Sparkline: Gives miniature chart within a single cell
      • =sparkline(C1R1:C2R2)
      • =sparkline(C1R1:C2R2, {"ymin", 0}) => Minimum value starts from "0"
      • =sparkline(C1R1:C2R2, {"charttype", "winloss"}) => Used for binary data
      • =sparkline(CR, {"charttype", "bar"; "max", MAX(C1R1:C2R2)}) => Used for single data representation
      • =sparkline(C1R1:C2R2, {"color", "#FFFFFF"; "linewidth", 2})
    • Text
      • =LEN(CR) => Length of text
      • =FIND(" ", CR) => Index of location of given value in text
      • =LEFT(CR, N) => Select text from left till "N" characters
      • =RIGHT(CR, N) => Select text from right till "N" characters
      • =TRIM(CR) => Trim blank spaces
      • =TRIM(LEFT(CR, FIND(" ", CR))) => Get first name
      • =TRIM(RIGHT(CR, LEN(CR)-FIND(" ", CR))) => Get second name
    • Text with Numbers
      • =C1R1 & " " & C2R2 => Combine both cells content
      • =JOIN(" ", C1R1, C2R2) => Join given cells with the separator provided
    • Conditional Formatting
      • =() =>
      • =() =>
      • =() =>
      • =() =>
    • Tricks
      • `Ctrl + `` => Shows formula in the cell
      • sheets.new => Open a blank sheet document directly through the browser

Steps

  • Data Entry
    • Navigation => Enter key to move below/ tab key to move right/ Alt + enter to move directly below
    • To delete the entry in a cell => Press delete key
    • To remove the modification you are making but not completed => Press esc key
    • To edit a entry in a cell => Click on the required cell > Use formula bar/ Double click on the cell/ Press F2 key
    • To change width of Rows and Column => Find thin cross on Rows & Column bar > Drag to required length
    • Fill Handle
      • To copy or continue a series of dates/ day/ month => Bring thin cross > Click and drag/ double click
      • Select the autofill option at the bottom of series to change type of series
      • Select two cell in a row and use fill handle to copy its pattern
      • Use Flash fill option to use smart fill feature
Share: