Microsoft Excel Features and Concepts that Everyone (Including Developers) Should Know

Self-Taught Route

Photo by Isaac Smith on Unsplash

Excel is probably one of the most common tools used in the workplace. As a software developer, you might never have to deal with Excel but you never know! Thus, spreadsheet knowledge and experience will always benefit you. Let’s be honest, the most commonly used aspects of Excel are not too hard to learn nor very time consuming to grasp.

If you are not a developer, but still looking for a job in which Microsoft Excel is a prerequisite, and you are wondering what the main things of this program are, then you should read the following outline. The goal of this outline is to save you time and help you avoid wasting your energy figuring out the main things about Excel. I won’t go into detail but remember, you can always google this and review Microsoft’s Office Support documentation.

Beginner Level

Basic Definitions:

• Spreadsheet / Workbook: Excel file or document.
• Worksheet: Single sheet within document.
• Column: Represented with letters.
• Row: Represented with numbers.
• Cell: Point where column and row intersects, such as “A1”. This is the point in which data (text, numbers, comments, hyperlinks, content) gets inserted.
• Headers: Title or name of a column.
• Dataset: blocks of data (cells filled in an organized manner) within document.

Ribbon Tabs (the top green bar):

• Home
• Insert
• Page Layout
• Formulas
• Data
• View
• Developer

Worksheet Tabs (bottom section of the document):

Where it says “Sheet1”, You can insert more sheets if necessary.

Data Types:

• Numbers (integers and decimals)
• Boolean (True or False)
• Date/Time
• Currency
• Strings (letters and common characters)
• N/A (null)

Format Cells:

This applies when converting a cell from one data type to another.

Main Math Formulas:

The following are the most commonly used based on experience.

• Sum
• Average
• Max
• Min
• Count
• Sumif
• Countif
• If
• Trim
• Concatenate
• Subtotal — for math operators in filtered data set.

Copy/Paste:

There’s a ‘paste special’ for pasting content (formula’s output) instead of the formulas.

Text Alignments:

Cells’ text can be aligned to the left, right and center.

Basic Computations:

Summation (+), subtraction (-) , multiplication (*) , division (/). You can apply these operators within cells.

Control + Z:

For undoing things if you accidentally press something else.

Control + Arrows:

For moving across dataset.

Excel Shortcuts:

There are many shortcuts (such as control + Z) that will save you time to make your life easier. Google these depending on what you are doing, since there are probably many!

Intermediate Level

Rounding Decimal Numbers:

You can determine the number of decimals within your cells.

Find and Select (and Replace):

It’s time consuming to look for values cell by cell. You can search for values and replace them simultaneously if necessary.

Add or Remove:

Columns, rows, or cells can be included or removed when editing your dataset.

Hiding and Un-Hiding Columns:

When datasets get too large you can apply this to quickly look at the most relevant data.

Swapping Columns:

This useful when you want to change the order of columns.

Transpose:

You can copy rows and paste it as a column, or vice-versa.

Flash Fill:

This applies for patterns. Formulas can get extended across or downwards.

Text File Importation:

You can import text files (.txt) and set appropriate delimiters for columns. For instance, if the imported text file is separated by commas, your commas will become the delimiter and data will get arranged as columns.

Save As:

Spreadsheets may be saved into other formats such as txt, csv, xls, etc.

Insert Tabular Table:

You may create a regular table by selecting a range of cells.

Data Filters:

Some datasets are huge, therefore adding filters into your dataset is a necessity.

Resize:

You may change the size of cells, hence, columns and rows.

Alt + Enter:

If you want to write a paragraph within a cell (although not ideal…) and split sentences, just press alt and then enter to create a new line within a cell.

Sort:

You may sort columns of data.

Remove Duplicates:

Depending on the situation you may remove duplicated values from columns.

Clear Content vs. Delete:

These are not the same. Deleting removes the entire cell. Clearing the content simply removes the cells’ output. For example, in a 5 by 5 table, deleting the middle column will make the remaining columns to shift to the left and will fill the deleted column. Clearing the content will empty the columns’ cell and leave these blank only.

Freeze Panes:

This will allow you to maintain your top row, first column, or both — first column and row, static as you scroll within the dataset. This is useful when you have huge datasets.

Making the Same Change Across Worksheets:

If you have many worksheets with similar tables or structure, instead of wasting time by making the changes one by one, you can right click the bottom tab and “select all sheets”. Making one change that will modify the rest of the sheets.

Conditional formatting:

This allows you to highlight cells depending on the condition implemented (if the cell’s value is greater than, less than, equal to certain value of your choosing).

Advanced Level

Text to Columns (button in ‘Data’ within Ribbon Tab):

This is when you split text from a group / column of cells, into new columns. For example, if column A has cells “first name, last name”, then you can split the first name and last name into 2 columns. At this point you only need to understand what delimiters are.

Create and Manipulate Charts or Graphs:

You need to make sure there are no discrepancies in your data (meaning that it’s well organized and formatted appropriately), before generating charts and graphs. These allow you to visually present your data or insights. In addition, there isn’t a one size fits all case… some charts or graphs work better than others depending on what you are presenting. Spoiler alert! Styling these can also be time consuming.

Pivot Tables:

This was, by personal experience a hard concept to grasp initially. You can simplify a large dataset by selecting a tabular table and converting it into a pivot table in a new worksheet. Then a window or pane will show up, and you should be able to drag and drop the fields (header names) that will allow you to give the desired shape for your pivot table (small summarized table).

V-lookup:

This function allows you to look for things in a table or range. For instance, you want to look for a student id associated with one student in a dataset with hundreds of rows.

Absolute and Relative Cell References:

• Relative cells are those that change based on the relative position of rows and columns. For example, “=C4+D4” will become “=C5+D5” on the next row.
• Absolute cells are those that will remain the same no matter the position of the formula. For example, “=$C$4+D4” will become “=$C$4+D5” on the next row. Note that $C$4 is the absolute cell.

Expert Level

The following is nice to have but NON-ESSENTIAL.

Macros:

A macro is a program that will automate repeated tasks. To create one, you need to record the exact steps in which something is done.

VBA Programming:

VBA is the language that allows you to create macros and other features.

Photo by Afif Kusuma on Unsplash

Should You Buy an Online Course to learn Excel?

No. Unlike a complex programming language, you don’t need to buy a course since Microsoft’s documentation is generally well written and clear. Most of this stuff you can figure it out by YouTube tutorials and by practicing.

How can I showcase my Excel skills?

By personal experience, if you really want to gain relevant experience with Microsoft Excel, you should do a project. For instance, you may launch a survey, collect the data, then you can do research based on that data; and finally present it if you want to go the extra mile. I guess doing a fancy project is more beneficial for someone that wants to be some sort of analyst. If you are a developer or want to become one soon, perhaps you should be aware of the concepts related to the previous bullet points only, but shouldn’t spend too much time on those.

Conclusion:

Knowing Microsoft Excel will always be beneficial for your skillset. It will never hurt you to be aware of the main concepts and features mentioned in the previous outline. Don’t spend too much overthinking this material. Excel has way too many stuff that is not essential, and its typically not too hard to learn.