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 key | Description | |
CTRL + B | Applies bold format | |
CTRL + 1 | Opens the Format Cells dialog box | |
CTRL + I | Applies 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
Referencing | Description | |
Absolute reference | Fixing the value of a particular cell reference. Cell reference are preceded by $. | |
Relative reference | The referenced cells are moving down as formula moves down | |
Mixed cell reference | Only part of the reference is fixed, either the row or the column, and the other part is relative |
4. Cell Modes
Mode | Description | |
Ready | Default mode , when you are not doing anything | |
Enter | The moment we begin typing text or value it automatically switches to enter mode | |
Point | When begin with = or + or - sign, it switches to enter mode, pressing any of the arrow keys , it switches from enter mode to point mode | |
Edit | If 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.
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).
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 No | Shortcut key | Description | ||
1 | Ctrl + Space | Selects an entire column | ||
2 | Shift & arrow keys | Selects multiple cells | ||
3 | Ctrl + | Insert Columns / rows | ||
4 | Ctrl - | Delete Columns / rows | ||
5 | Ctrl + Home | Get back to the first cell | ||
6 | Ctrl + x | Cut | ||
7 | Ctrl + C | Copy | ||
8 | Ctrl + V | Paste | ||
9 | Ctrl + T | Insert a table | ||
10 | Tab or -> | Moves to the immediate right | ||
11 | <- or Shift + Tab | Moves to the immediate left | ||
12 | Enter | Moves to the below cell | ||
13 | Ctrl + Enter | Data/formula is copied to all the selected cells | ||
14 | F2 | End of the text of the selected cell | ||
15 | Ctrl + R | Fill right | ||
16 | Ctrl + D | Fill down | ||
17 | Alt + = | Sum | ||
18 | F4 | Relative reference | ||
19 | F4 (once) | Get the dollar sign for both the column and row | ||
20 | F4 (twice) | Get the dollar sign only for the row | ||
21 | F4 (thrice) | Get the dollar sign only for the column | ||
22 | F4 (4 time) | Goes back to relative referencing | ||
23 | Home | Cell in column A of the current row | ||
24 | Ctrl + End | The last cell of the so-called active area of the worksheet | ||
25 | Ctrl + -> | First occupied cell to the right in the same row | ||
26 | Ctrl + <- | First occupied cell to the left in the same row | ||
27 | Ctrl + arrow up | First occupied cell to the top in the same row | ||
28 | Ctrl + arrow down | First occupied cell to the bottom in the same row | ||
29 | Ctrl + Page down | The cell pointer's location in the next worksheet of that workbook | ||
30 | Ctrl + Page up | The cell pointer's location in the previous worksheet of that workbook | ||
31 | Alt+ FX or Alt +F4 | Quit excel | ||
32 | F12 | Save as | ||
33 | Ctrl + O | Open | ||
34 | ctrl + N | New | ||
35 | Ctrl + S | Save | ||
36 | Ctrl + P | |||
37 | Ctrl + W or Alt + FC | Close the current file | ||
38 | Ctrl + F | Find | ||
39 | Ctrl +H | Replace | ||
40 | Ctrl + K | Insert hyperlink | ||
41 | Ctrl + 1 | Format cells | ||
42 | F7 | Spelling | ||
43 | Ctrl + Z | ndo | ||
44 | Ctrl + Y | Redo | ||
45 | F1 | Help |
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 !