Day 01  of 100 Days Of Code in Data Analytics : Microsoft Excel Fundamentals

Day 01 of 100 Days Of Code in Data Analytics : Microsoft Excel Fundamentals

Hello there, pals !

This is the first day of my #100DaysOfCode in Data Analytics. To be honest, I had no idea where to start with this challenge's tech stack. After that, I tweeted the same thing, and several aspiring data analysts advised me to start with Excel. As a result, I chose to begin with Microsoft Excel.

Excel , But why ?

Microsoft Excel is one of the most widely used data analysis programmes. They are without a doubt the most sought-after analytic tool accessible, as they come with built-in pivot tables. It's a one-stop data management solution that lets you import, examine, clean, analyse, and display your data with ease.

Here are a few things I've discovered:

Excel User Interface

Basic Formatting

Absolute vs Relative vs Mixed Cell Referencing

Cell Modes

Possible Data types

1. Excel User Interface

  • Ribbon tabs
  • Name box
  • Formula bar
  • Columns
  • Cells
  • Rows
  • Ribbon groups
  • Ribbon controls
  • Sheet tabs
  • Split controls - Horizontal & Vertical
  • Status bar
  • Quick access toolbar
  • Title bar

2. Basic formatting

Shortcut keyDescription
CTRL + BApplies bold format
CTRL + 1Opens the Format Cells dialog box
CTRL + IApplies Italic format
CTRL + Shift + ~Applies general format
CTRL + Shift + $Applies currency format
CTRL + Shift + %Applies percent format
CTRL + Shift + ^Applies scientific format
CTRL + Shift + #Applies date format
CTRL + Shift + @Applies time format
CTRL + Shift + !Applies number format

3. Absolute vs Relative vs Mixed cell referencing

ReferencingDescription
Absolute referenceFixing the value of a particular cell reference. Cell reference are preceded by $.
Relative referenceThe referenced cells are moving down as formula moves down
Mixed cell referenceOnly part of the reference is fixed, either the row or the column, and the other part is relative

4. Cell Modes

ModeDescription
ReadyDefault mode , when you are not doing anything
EnterThe moment we begin typing text or value it automatically switches to enter mode
PointWhen begin with = or + or - sign, it switches to enter mode, pressing any of the arrow keys , it switches from enter mode to point mode
EditIf we return to a cell that contains data and press F2 or click in a formula bar, we are placed in edit mode

5. Possible data types

Excel continually analyses what you write and categorises it as one of three potential data types: text, value, or formula.

  1. If Excel recognises an item as a formula, it calculates the formula automatically and shows the result in the worksheet column (you continue to see the formula itself, however, on the Formula bar).

  2. If Excel detects that the item is not a formula, it assesses whether it should be categorised as text or a value.

Excel distinguishes between text and values in order to determine how to align the item in the worksheet. Text entries are aligned with the cell's left edge, whereas values are aligned with the cell's right edge.

Bonus tip :

Here's a list of shortcuts I discovered today.

Sl NoShortcut keyDescription
1Ctrl + SpaceSelects an entire column
2Shift & arrow keysSelects multiple cells
3Ctrl +Insert Columns / rows
4Ctrl -Delete Columns / rows
5Ctrl + HomeGet back to the first cell
6Ctrl + xCut
7Ctrl + CCopy
8Ctrl + VPaste
9Ctrl + TInsert a table
10Tab or ->Moves to the immediate right
11<- or Shift + TabMoves to the immediate left
12EnterMoves to the below cell
13Ctrl + EnterData/formula is copied to all the selected cells
14F2End of the text of the selected cell
15Ctrl + RFill right
16Ctrl + DFill down
17Alt + =Sum
18F4Relative reference
19F4 (once)Get the dollar sign for both the column and row
20F4 (twice)Get the dollar sign only for the row
21F4 (thrice)Get the dollar sign only for the column
22F4 (4 time)Goes back to relative referencing
23HomeCell in column A of the current row
24Ctrl + EndThe last cell of the so-called active area of the worksheet
25Ctrl + ->First occupied cell to the right in the same row
26Ctrl + <-First occupied cell to the left in the same row
27Ctrl + arrow upFirst occupied cell to the top in the same row
28Ctrl + arrow downFirst occupied cell to the bottom in the same row
29Ctrl + Page downThe cell pointer's location in the next worksheet of that workbook
30Ctrl + Page upThe cell pointer's location in the previous worksheet of that workbook
31Alt+ FX or Alt +F4Quit excel
32F12Save as
33Ctrl + OOpen
34ctrl + NNew
35Ctrl + SSave
36Ctrl + PPrint
37Ctrl + W or Alt + FCClose the current file
38Ctrl + FFind
39Ctrl +HReplace
40Ctrl + KInsert hyperlink
41Ctrl + 1Format cells
42F7Spelling
43Ctrl + Zndo
44Ctrl + YRedo
45F1Help

Acknowledgement :

This blog simply represents my own ideas and opinions (based on my limited knowledge) and should not be used as a replacement for legitimate sources. I'd welcome corrections in the comments if I ever make a mistake or if you disagree!

End note :

These were the things that I was able to explore today, and I am looking forward to doing additional hands-on learning tomorrow. If you have any further information on the aforementioned concepts, please share it in the comments section below. Don't forget to connect me on Twitter and Github.

Have a great time !