Microsoft Excel is a spreadsheet program that has been around since 1985 and chances are high that even if you don’t know how to use it, you are familiar with what it does. It is currently among the most used software in the workplace today, particularly when it comes to the business side of things.
Excel allows you to add data to its spreadsheets, analyze the data in the form of charts and graphs, predict future trends and more. For businesses, this means that Excel can be used for basic accounting purposes, for calculating return on investments and more.
Who Uses Excel?
Excel is a program that mostly everyone can benefit from, but businesses and marketers are among the top users.
Uses of Excel:
Excel can be used for a number of different tasks, including:
- Basic Accounting – For tracking deposits, expenses and budgets, Excel can be used as a basic accounting tool.
It is capable of making basic mathematical calculations like adding, subtracting, multiplying and dividing, but it is also capable of more advanced calculations as well. It can even summarize trends (for example, trends for monthly and yearly expenses).
- Product Management – Excel can keep track of product sales on a yearly, monthly or quarterly basis and be used to predict future values. Another practical use when it comes to Excel is being able to see returns on investment.
- Customer Data – Excel is often used to store customer data that changes over time.
- Scheduling – Not only is Excel used for basic accounting purposes and more, but it can also be used for timetable purposes and to schedule events.
Here are some tips on how to make your business more productive with Excel.
Businesses can also use Yocale’s Business Analytics Dashboard to keep track of sales, new client bookings, metrics from marketing promotions and more.
For even more scheduling tips for your business, it may be time to set up an editorial calendar. Here is your guide to setting up an editorial calendar.
The main disadvantage of Excel (there aren’t many!) is that, because of its advanced capabilities, it can take quite a lot of time to actually learn how to use it. Another problem is that beginners often don’t utilize the program as practically as they could.
This guide, however, will offer you a step-by-guide to the basics of Excel, pointing out all of the important and necessary features that beginners should take time to learn.
When it comes to the best way to learn Excel online, here is a list of resources. Ensure that you are learning the most recent version of Excel and not an older program by searching for how to use Excel 2017.
Even though this guide merely covers the basics, many businesses and marketers accomplish everything they need to with Excel just with its basic features.
Without further ado, here is your guide to everything you need to know about Excel – how to learn Excel quickly and easily – taking you through the necessary formulas you need to know, keyboard shortcuts, as well as some useful tips and tricks.
How To Use Excel: The Basics
- Rows, Columns and Cells – Rows are on the left side of a given spreadsheet and are identified with numbers. Columns run at the top of the sheet and are identified with letters. When a row and column meet (for example, B5), B5 is called a cell.
- Ranges – When you highlight multiple cells at once, this is called a cell range. You identify cell ranges with a colon in the middle. For example: D3:D6.
Keep in mind that the “data” you enter into these cells can be numbers, text, dates, etc.
Next, here are all of the basic tasks that Excel users should know how to do.
1. Sort and Filter Data
Now that you have familiarized yourself with how a spreadsheet works, one of the first things all beginners should learn how to do is to sort and filter data.
What It’s Useful For: Excel is useful for all list sorting purposes (for example, alphabetizing lists, putting numbers in order based on value). It’s also great for filtering data, on the other hand, as the program makes it possible to see a small amount of data at one time, depending upon what part of the data you want to look more closely at.
Keep in mind that when filtering data, the original data is still intact, but you just won’t be able to see it.
Here’s how to sort a list:
1. Place your cursor in the blank cell above the column you want to sort
2. From the toolbar (aka the ribbon), select ‘Data’, click ‘Quick Sort’.
Here’s how to filter a list:
1. Place your cursor in the data table
2. On the ribbon, select ‘Data’ and then ‘Filter’
3. Drop-down menus will appear so you can filter your data by item, month, etc. – whatever data you want to see.
4. Select ‘OK’
When it comes to Excel, you hear the term “PivotTables” thrown around a lot. In short, PivotTables are used to reorganize data.
Why It’s Useful: They can be used to summarize values and analyze data. This is a particularly excellent tool for businesses.
The benefit about using PivotTables is that they can be created in a matter of seconds.
2. Create a PivotTable
Simply go to ‘Data’ and then select Pivot Table. Once you do this, you have a few different options to choose from.
- Report Filter – This allows you to look at the data from specific rows.
- Column Labels (Headers)
- Row Labels
- Value – This allows you to manipulate your data by any number of means, including sum, count, average, etc.
3. Adding More Rows or Columns
Instead of manually adding more rows or columns, this will save you a lot of time:
1. Highlight the number you want to add
2. Right click and select ‘Insert’
4. Formulas + Functions (The Ones You Need to Know)
In addition to the tasks above, no guide to Excel is complete without a discussion of its formulas and functions. Excel has an extensive list of formulas/functions, but the ones included on this list are the ones every user should know how to use.
The best way to learn Excel formulas is with practice. Over time, you can search for a more advanced Excel tutorial.
What are formulas?
Excel formulas are essentially equations that you will enter into a cell on the spreadsheet, which will make calculations (such as adding, subtracting and more) based on the values you have entered.
In terms of how to use Excel formulas, simply start off each formula by typing in the equals sign (=), which tells Excel to make a calculation.
Why use formulas? The benefit of using formulas is that they are able to make calculations faster than an actual calculator, because you can alter numbers in any category and they result equations will be automatically calculated, saving you valuable time when you are working with data.
For example, if you were keeping track of the quantities of different products in your warehouse, you simply have to alter the new value and the calculations will immediately be made, which means you don’t have to add up each and every value again.
Now, there is one important thing to point out. We have been using formulas and functions interchangeable as general terms, but they are different when it comes to Excel. Excel’s formulas also contain functions. Functions describe the actual code.
For example, the SUM function will add up data for you, while the formula contains the actual values in it.
The output, for reference, is the answer to the equation.
Here is a list of Microsoft’s most popular functions.
Without further ado, here is a list of Excel’s most important formulas, listed briefly to serve as an overview. Feel free to read into any of them in more depth if you think they might be of use to you or your business!
Formula: =SUM(6, 8) or =SUM(A5, B5) or =SUM(A6:B7)
The sum formula will add numbers for you. You can add two numbers together separated by a comma, numbers that are in different cells (which are also separated by a comma. Example: A1, B1) or you can add the values in a number of different cells by separating them with colons.
Here is how you would use this function, which is a method you can apply to the rest of the formulas below as well.
Keep in mind that an easy way to learn Excel formulas is to find an online tutorial and follow along in Excel.
1. Select the cells you want to add
2. Go to the toolbar and select the ‘Formulas’ tab
3. You will see ‘Function Library’ and from here, you can select ‘Sum’
IF(logical_test, value_if_true, value of false)
The IF function, one of Excel’s most popular functions, is used to apply a certain condition to your data.
The count formula counts a range of cells that have numbers. Keep in mind that this function will only count the cells that have numbers in them.
As you can see, Count and Counta are very similar.
Counta, however, will count a range of cells that have any kind of data in them, from numbers to dates to text.
The LEN formula counts the total number of characters in a cell, including the number of spaces in the formula.
Just as the name would imply, this formula trims any space in a cell, with the exception of single spaces between words. This formula is useful in the event that you copy data into Excel and find that there are spaces in front of (or behind) it, which is actually quite common.
RIGHT, LEFT, MID
Formulas: =RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters)
This formula allows you to pull out specific characters or numbers (substrings) from one string of data. For example, perhaps your string of data looks like this: 55456, but you only need the substring ‘4.’
Formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
The VLOOKUP function allows you to combine two sets of data on two separate spreadsheets into one.
This function will combine data from multiple (or more) cells into one.
Formula =CONCATENATE(A1, B1)
As the name would imply, this function allows you to calculate the average in a given set of data.
MAX & MIN
Similarly, this allows you to calculate the maximum and minimum values in a given set of data.
Formulas: =MAX(range_cells) =MIN(range_cells)
How to Create Charts
Excel makes it possible to create different charts and tables out of the data you provide it with. Here is a step-by-step guide on how to create two types of charts: the pie chart and the line chart.
- How to Create a Pie Chart
Create a pie chart when you want to show data as part of a whole and it is very easy to so.
- Highlight the Relevant data that you want to include in your pie chart.
- Select the Insert Tab from the toolbar at the top of Excel.
- In the Charts Group, click on the Pie Chart button (you will see an image of a pie graph). This is also where you would go if you wanted to create a PivotChart.
You can choose what type of pie chart you want (whether you prefer a 2-D pie chart, 3-D, etc.), and you can see what the final result will look like by hovering your mouse over each option via the drop down menu.
- How To Create a Line Chart
A line chart connects a series of data points with a line. In this way, a line chart is a great chart to illustrate changes in trends over time, such as business growth, product demand, etc. To create a line chart, follow these steps:
- Highlight the relevant data that you want to include in your line chart
- Select the Insert Tab from the toolbar at the top of Excel. In the Charts Group, click on the Line Chart button (you will see an image of a pie graph, though). As you will see, you can choose from a selection of different types of line charts.
Excel Keyboard Shortcuts
According to Microsoft, the following is a list of the some of the most common Excel keyboard shortcuts.
- Home Tab (Most Frequently Used) (Alt + H)
- Save a Spreadsheet (Ctrl + S)
- Copy (Ctrl + C)
- Paste (Ctrl + V)
- Fix a Mistake (Ctrl + Z)
- Clear Cell Contents (Delete key)
- Cut Cells (Ctrl + X)
- Bold (Ctrl + B)
Here’s a few extra, very useful keyboard shortcuts in addition to the most common ones.
- Open “Format Cells” (Ctrl + 1)
- Autocomplete Function Name (Tab —> Enter =, begin typing the function name (‘su’ for SUM, press Tab)
- View/Hide the Ribbon (Ctrl + F1)
- Select Entire Column (Ctrl + Space)
- Select the Entire Row (Shift + Space)
Keep in mind that the list of keyboard shortcuts is endless, so these are just the main ones youa re likely to find useful early on. Here’s another list of more advanced Excel keyboard shortcuts that covers navigation shortcuts, formatting shortcuts and more.
Tips & Tricks
- Select All with One Simple Click
While most people are familiar with using the keyboard shortcut Ctrl + A to select all, there is actually a shortcut for a shortcut! In the corner of the spreadsheet, at the intersection of A1, simply click on the button with the white triangle in it.
- Keep Your Features/Formatting Handy with Quick Analysis
Think of Quick Analysis as your Excel assistant. It is a small menu of all Excel’s most common tools, kept handy at the bottom of the screen for quick accessibility.
Every time you want to create a table or do some formatting, this little menu will prevent you from wasting time looking for it in the ribbon.
- For Easy Access To Excel’s Formulas
Instead of writing your own formulas, follow this method to get easy access to Excel’s list of formulas.
Simply go to ‘Formulas’ tab >’Function Library’ and you will find all of Excel’s formulas.
- Predict Trends
One of Excel’s newer features is the ability to analyze data and predict trends from that data via the program’s range of forecasting tools. To do so, go to Data > Forecast sheet.
Excel not only provides a lot of basic tools, but its methods of analyzing data and forecasting future trends make it a tool that all businesses can (and probably should!) benefit from.
With all of Excel’s capabilities, it is easy to see why businesses should begin implementing it and get started on their path to Excel training soon.