Blogbrew538

• Excel For Beginners Powerpoint
• Spreadsheets For Beginners
• Free Excel Training For Beginners
• Excel For Beginners Step By Step

Excel Made Easy: a Beginner's Guide to using Microsoft Excel. I use Excel 2010 for Windows in the videos but any version with Excel 2007 for Windows or later would work. Excel Versions prior to 2007 have a different user interface but concepts will apply. As the course is targeting beginners, it has been developed to be very easy to follow. A Beginner’s Guide To Getting Started 1: Grab your free exercise file here! 2: Opening a spreadsheet 3: Working with the Ribbon 4: Managing your worksheets 5: Entering data 6: Basic calculations 7: Unlocking the power of functions 8: Saving and sharing your work 9: Welcome to Excel.

If you’re a complete beginner when it comes to Microsoft Excel, then you’ve come to the right place to get started and learn how to use it. This free beginner’s guide will help you to understand the basics of Excel and provide you with practical examples and tips. If you don’t know what a VLOOKUP is or what a SUMIF does, don’t worry, by the end of this guide, you’ll have a clearer idea of what they are and what they can do for you.

Keep in mind that this guide can also be applied to Google Sheets, which is a very similar spreadsheet program that is available to anyone with a Google account.

You may be thinking that Excel looks complicated, boring and perhaps unnecessary. But it can be less complicated if you focus on certain tasks and understand how Excel can help you. As for being boring and unnecessary, well we won’t lie. Unless you’re working on something you love or find interesting, it is likely that spreadsheets relating to that work aren’t going to be the highlight of your day, so yes it can be boring to use Excel.

Being unnecessary is something that depends on your judgement, however. Excel can be used for many things, but that doesn’t mean it has to be. If there is a more efficient way of doing things without using Excel, then it’s probably best you don’t use it. But in most cases, especially when lots of information is involved, it’s best to use Excel to calculate and present data and results.

This guide will start by explaining some of the benefits and value you can gain from using Excel, particularly as a job hunter or business owner. It will then cover some of the fundamentals of Excel that will get you started with using it.

This guide covers the following contents:

• How Can Excel Benefit My Career or Business?
  • Excel for job hunters – communication, decision making, and multi-tasking and organisation.
  • Excel for new business owners – talks and negotiations with suppliers, budgets and finances, and customer ledger.
• How Can I Use Excel?

Use the links above if you’d like to navigate to a certain section of the guide.

How Can Excel Benefit My Career or Business?

Excel has a lot of potential to develop your skills in more ways than it may appear. It can lend itself to your credentials by showing that you’re capable of handling, interpreting, and communicating data and ideas effectively. It can also be an immensely helpful tool when you’re a budding business owner, saving you the time and effort on trying to track data on paper.

Let’s look at how Excel can help you as a prospective employee or entrepreneur:

Excel For Job Hunters

Excel skills are a valued attribute on your CV or resume. Many employers will expect you to already have a decent understanding of how to use it and often during the interview process for a new job, employers will ask you to complete practical tests to assess your relevant knowledge and skills. Excel tests are commonly used as part of this process.

If you’re currently looking to update your CV or resume and are trying to identify what skills are in demand, it’s a good idea to check leading job sites like Indeed, Reed and LinkedIn to see what skills and qualifications they say employers are looking for. For example, Indeed produced an article which talks about the top 10 job skills for any industry.

“Having competitive job skills is an important part of developing your career. There are many qualities that are universally desired by employers regardless of their field. Especially if you are unsure about the career path you would like to pursue, it is important to develop skills that can transfer from one industry to another. This allows you to explore your job options freely while still creating a strong resume and performing well at work.”

“10 Top Job Skills for Any Industry: Transferable Skills You Need” – Indeed, 2020

More often than not, you will see certain skills repeatedly mentioned by most job sites and in many job adverts. Those skills are usually: Communication, Decision-Making, Multitasking and Organisation. Excel can help with all of these skills.

Communication

Whether you’re talking to customers or holding a meeting with colleagues, you need to be confident in what you’re saying. Excel can help by giving you the ability to record and monitor information, which can assist you when you’re advising customers. For example, Excel could tell you how many items are in stock and when the next delivery of a certain item is due. It can also help you when in meetings with colleagues, such as by providing you with accurate graphs and tables to clearly demonstrate how sales are performing that month.

Having information and data to back up what you are communicating is not to be underestimated and is certainly a good thing.

Decision-Making

Making a decision could be easy or it could be hard. It may be easy because you’ve been in the situation before and you made the right decision last time, or hard because the situation is new and complex. You can’t make a good decision without first understanding what is going on. In some cases, there isn’t the time or the data to sit down and work things out in Excel.

However, where there is time and where there is data available, Excel can play a big role in your decision making. Everyone has to base their decisions on something, be that past experience or information and data. Excel can be used to help you identify patterns and trends which can then form part of your decision-making process. Referencing accurate data and relevant evidence provides you with a solid argument when explaining the logic behind your decisions.

Multitasking and Organisation

We’ve grouped multitasking and organisation together here as they are similar in many ways. If you’re multitasking, you’re probably going to need to prioritise in what order your many tasks should be done and which of those tasks can be completed at the same time.

Organisation is not so different. Excel can help by giving you the ability to plan out and coordinate tasks, whilst also allowing you to assign each task attributes like value and time. Once all the tasks are detailed in your spreadsheet, you can easily order them by the tasks that are most or least valuable or by the tasks that will take the longest or shortest time to complete.

At the time of writing, the COVID-19 pandemic is causing heartbreak and tragic loss around the world. Many companies and industries have shut down during the pandemic, and in some cases there are businesses that will either never reopen or will reopen but with a much-reduced number of employees. The Coronavirus Job Retention Scheme has allowed businesses to furlough staff with the UK Government covering 80% of an employee’s wages up to a maximum of £2,500 per month, but from August 2020 businesses will need to contribute to the cost. Sadly, this is likely to result in some businesses making staff redundant.

If you’re at risk of redundancy, currently furloughed, unemployed, back at work or working from home, there is enough going on right now without cramming your head full of Excel formulas, but if you’re reading this it’s probably because you have some time available to start learning a new skill or to try and build on what you already know. We hope you find this guide useful and we truly wish you all the best.

Excel For New Business Owners

The entrepreneurs amongst us who are starting their own business will definitely have a use for Excel in some way, shape or form. Working out your startup budget? Excel can help with that. Forecasting your sales figures for the coming months? Excel can help with that. Making a cup of coffee? Excel cannot help with that, sorry.

When running your own business, there are a lot of things to consider and keep track of. There are suppliers to negotiate with, schedules to maintain, customers to meet, rent and taxes and assorted bills to pay – all this and more is whirling around in the head of a business owner. Excel can assist with many of these.

Talks and negotiations with suppliers

If you’re talking with a lot of suppliers, Excel can be used to list what products a supplier offers and at what prices. Once you’ve met with all the suppliers, you can simply review the details you’ve kept in Excel to determine who offers what you need at the best price.

Budgets and finances

You can also use Excel to calculate budgets and keep track of income and expenses, to ensure your finances are in good shape. This will come in very handy when you have to submit quarterly and annual details to HMRC and Companies House, such as VAT returns, corporation tax, income tax self-assessment and confirmation statement. Even if you plan to use an accountant, having a clear, up to date and accurate record of your company finances will certainly help you and them.

Customer ledger

As long as you’re operating legally and in accordance with data protection regulations – note that you’re highly likely to be storing personally identifiable information (PII) for this – you could use Excel to keep a customer ledger, so you can monitor which clients have paid you in full, which have paid a deposit and which are yet to make a payment.

Whatever your needs may be, Excel is sure to have a feature or two that enhance your business skills and make your life a little, or a lot, easier if used well.

How Can I Use Excel?

Think of Excel as a clever record keeper and calculator rolled into one.

First, let’s get you introduced to the basics of Excel: Cells, Columns, Rows, Worksheets/Tabs, Formulas and Charts/Graphs.

Cells

A Cell in Excel is an individual box within a Worksheet/Tab and is usually used to input and hold numeric or text data. Each Cell has a name, that name comes from the Column and Row the Cell sits on. If you’ve ever played the game Battleship, used grid references on a map or had allocated seat tickets for a train/aeroplane/venue, you’ll easily understand Cell names.

Here is an example: Columns run left to right along the top of the Worksheet/Tab and are labelled according to the alphabet. Rows run down along the left side of the Worksheet/Tab and are labelled numerically. So if you’re looking at a Cell which sits on Column A, Row 1, the Cell’s name will be A1.

Columns

Columns run left to right along the top of the Worksheet/Tab and are labelled according to the alphabet. Each Column is a vertical series of Cells.

Rows

Rows are labelled along the left side of the Worksheet/Tab numerically and run down from top to bottom. Each Row is a horizontal series of Cells.

Worksheets/Tabs

Worksheets/Tabs are made up of Columns, Rows and Cells and are essentially pages of your Excel workbook. You can have multiple Worksheets/Tabs within your Excel workbook and they are a useful way to separate different types of data and information.

Unlike Columns, Rows and Cells, you can rename Worksheets/Tabs. By default, Worksheets/Tabs are named ‘Sheet1’, ‘Sheet2’ etc, but you can rename them and even colour code them if you wish by right-clicking on the Worksheet/Tab and selecting either ‘Rename’ or ‘Tab Color’.

Formulas

Excel Formulas can be used to either calculate the value of a single Cell or multiple Cells, as well as use Functions to calculate values or retrieve data.

Simple Formulas can calculate by adding, dividing, multiplying or subtracting values from other Cells. To add, you use the + symbol, to divide you use the / symbol, to multiply you use the * symbol and for subtracting you use the – symbol. For example, if you wanted to add together the values of three Cells to work out the total value, you could do this:

Here, you would select the first Cell and type the + symbol, then select the second Cell and type the + symbol again, and finally select the third Cell to create a Formula that adds together all three Cells and calculates their total value. This is not a problem if you are working with a handful of Cells, but if you’re dealing with a lot of them, it is much easier to use the SUM Function and highlight the Cells to achieve the same calculation of adding them all together, like this:

Using the SUM function to create a Formula can be a real timesaver when you’re working with a lot of data. In the example above, it shows how you can highlight multiple Cells in a Row; this also works if you need to highlight multiple Cells in a Column.

If you need to bring together data from multiple sources or from different parts of your spreadsheet, you can use Functions like a VLOOKUP, a SUMIF or a COUNTIF to retrieve certain information and/or give you an overall picture of the data you have.

VLOOKUP

For example, imagine you own a restaurant and you’ve got a long list of table reservations. The list contains customer contact details, party size, date the table is booked for and allergy/dietary requirements. However, you’ve forgotten to ask what time they will be arriving! Luckily, you’ve got email addresses for all of the customers, so one of your colleagues sends an email to each of them and makes another list that just has the customers’ email addresses and time of arrival. You can then quickly retrieve the times from your colleague’s list and store them alongside the correct customer in your original list by using a VLOOKUP, like this:

So, what is this VLOOKUP doing? First, you have to ensure that there is a common identifier in both your lists. The common identifier must be identical in each list – in this case the common identifier is customer email addresses. The Column containing the common identifier needs to be located to the left of the data you want to retrieve.

So, we start off by writing =VLOOKUP( then select the ‘lookup_value’, which in this example will be the first email address in Cell A2, then type in a comma.

Your Formula should now look like this: =VLOOKUP(A2,

Next, you’ll need to go to the Worksheet/Tab that contains the other list (in this example, the Worksheet/Tab has been named ‘Times’) and select the ‘table_array’, which means highlighting the range of Columns starting with the Column containing the ‘lookup_value’ and continuing until you reach the Column containing the data you want to retrieve, then type in a comma. So for this example, in the ‘Times’ Worksheet/Tab, we’re selecting Columns A and B, Column A contains the ‘lookup_value’ and Column B contains the table reservation times.

Your Formula should now look like this: =VLOOKUP(A2,Times!A:B,

We’re almost finished…

Next, we need to type in the ‘col_index_num’ which just needs you to count along from the first Column in your ‘table_array’ until you get to the Column which contains the data you want to retrieve. In this example, the data we want is in the second Column in the ‘table_array’ so we just need to type 2, then a comma.

Your Formula should now look like this: =VLOOKUP(A2,Times!A:B,2,

Finally, we need to choose a ‘range_lookup’. There are two choices here: ‘TRUE’ or ‘FALSE’. ‘FALSE’ is usually the best option to choose as it means that the VLOOKUP will find the exact match to the ‘lookup_value’ you selected at the beginning of this Formula. Once you’ve selected or typed in ‘FALSE’, you can just hit Enter on your keyboard and the VLOOKUP will use the details you’ve selected and input, then go and find the data you wanted. Once it finds this, it’ll place it in your original Bookings list.

Your finished Formula should now look like this: =VLOOKUP(A2,Times!A:B,2,FALSE)

As shown in the image above, once you’ve written this VLOOKUP you can locate the Fill Handle in the bottom right corner of the Cell (in this example, the Cell is E2) and drag the handle down to the bottom of your list. The VLOOKUP Formula will change for each Cell and retrieve relevant data based on each customer email address.

This is just one example of how to use a VLOOKUP. There are many other applications and they don’t all need to be focused on email addresses. You might want to use order/invoice reference numbers or customer usernames instead.

SUMIF

There may come a time when you need to add up values in a particular Column, Row or specific Cells based on certain criteria. For example, imagine you own a restaurant and you have a list of all your bookings, the list contains customer contact details, party size, date and time the table is booked for and allergy/dietary requirements. Now, if you wanted to add up how many people are going to attend your restaurant on a certain day, you can use a SUMIF like this:

Let’s look at what this SUMIF is doing. We’re trying to add up the total number of customers arriving on certain days and the SUMIF allows us to do that. First, we need to write =SUMIF( in Cell B2, just under the appropriate heading ‘Total Number of Customers’, then go to the Worksheet/Tab that contains the list of all your bookings (in this example, the Worksheet/Tab has been named ‘Bookings’) and select the ‘range’, which means highlighting a single Column, a single Row, or a specific range of Cells that contains the information you want to search and match, then type a comma. So, for this example, in the ‘Bookings’ Worksheet/Tab we’re selecting Column D as it contains all the date information for each booking.

Your Formula should now look like this: =SUMIF(Bookings!D:D,

Next you’ll select the ‘criteria’, which in this example will be the first date in Cell A2 of the ‘Totals’ Worksheet/Tab, then type a comma.

Your Formula should now look like this: =SUMIF(Bookings!D:D,Totals!A2,

Finally, we need to go to the Worksheet/Tab that contains the list of all your bookings and select the ‘sum_range’, which means highlighting a single Column, a single Row, or a specific range of Cells containing numeric data that you want to add up and calculate a total value for. So for this example, in the ‘Bookings’ Worksheet/Tab we’re selecting Column C as it contains all the party size information for each booking. Once you’ve selected the ‘sum_range’ you can just hit Enter on your keyboard. The SUMIF will then use the details you’ve selected and input to go and find the data you wanted and calculate the total value.

Your finished Formula should now look like this: =SUMIF(Bookings!D:D,Totals!A2,Bookings!C:C)

As shown in the image above, once you’ve written this SUMIF you can locate the Fill Handle in the bottom right corner of the Cell (in this example, the Cell is B2) and drag the handle down to the bottom of your list. The SUMIF Formula will change for each Cell and calculate the total number of customers due to arrive for each date.

COUNTIF

You may find that you need to count how many times a certain value appears in a Column, Row or specific Cells. For example, imagine you own a restaurant and you have a list of all your bookings. Each row of the list relates to a booking and contains customer contact details, party size, date and time the table is booked for, and allergy/dietary requirements. Now, if you wanted to count how many bookings have been made for certain days you can use a COUNTIF like this:

Let’s look at what this COUNTIF is doing. We’re trying to count how many bookings have been made for certain days and the COUNTIF will work that out for us. First, we need to write =COUNTIF( in Cell C2, under the heading ‘Total Number of Bookings’, then go to the Worksheet/Tab that contains the list of all your bookings (in this example, the Worksheet/Tab has been named ‘Bookings’) and select the ‘range’, which means highlighting a single Column, a single Row, or a specific range of Cells that contains information you want to search and match, then type a comma.

So, for this example, in the ‘Bookings’ Worksheet/Tab we’re selecting Column D as it contains all the date information for each booking.

Your Formula should now look like this: =COUNTIF(Bookings!D:D,

Finally, you’ll select the ‘criteria’ which in this example will be the first date in Cell A2 of the ‘Totals’ Worksheet/Tab. Once you’ve selected the ‘criteria’ you can just hit Enter on your keyboard and the COUNTIF will use the details you’ve selected and input to go and find the data you wanted and count how many times the data can be found.

Your finished Formula should now look like this: =COUNTIF(Bookings!D:D,Totals!A2)

As shown in the image above, once you’ve written this COUNTIF you can locate the Fill Handle in the bottom right corner of the Cell (in this example, the Cell is C2) and drag the handle down to the bottom of your list. The COUNTIF Formula will then change for each Cell and count the total number of bookings due to arrive for each date.

Charts and Graphs

Excel Charts and Graphs can be really useful to visualise data and give a clearer picture of what the data can tell you.

Looking at numerous Columns and Rows full of various information can be a bit hard on your eyes and in some cases it just looks meaningless. This is where Charts and Graphs can help by showing the data in a different way, which may result in you spotting some sort of trend or pattern.

To create a Chart or Graph in Excel you’ll first need some data. Following on from the examples above, we have some data relating to restaurant customer numbers and booking numbers for certain dates.

Excel is smart, so if you highlight cells which contain things like a series of dates, headings and data you can go to the ‘Insert’ section, which is located towards the top-left of your screen, and select ‘Recommended Charts’. This will then present you with some appropriate charts and graphs, like so:

Before we end, let’s give you another practical example of how Excel could be used based on what we’ve already covered.

Using Excel to Create a Budget Sheet

We’ve mentioned budgets earlier in the guide, but now let’s look at how Excel can be used to create a budget. We’ll start by opening a blank workbook in Excel and then begin writing some appropriate headings (e.g. Outgoings, Name, Category, Months).

A first tip to make a note of is if you’re entering information like dates or months into Excel, it is smart enough to recognise what you’re trying to do. If you locate the Fill Handle in the bottom right corner of the Cell that you entered the first date/month in and drag up/down/sideways as demonstrated below, it will automatically input the next dates/months into the Cells you drag the handle over.

For example, here we’ve entered ‘Jan’ into Cell D2 and then dragged the Fill Handle across to the right all the way to Cell O2. Once we’ve finished dragging the handle across, the month names appear in the Cells.

Continue to enter appropriate headings both across Columns and Rows, then enter the names and categories for each item of your budget into the relevant Cells. For example, imagine you own a restaurant and you’re trying to create a budget which details outgoing spend and incoming revenue. The names and categories for each budget item might look something like this:

This details outgoings such as staff salaries, repair costs, utility bills and food/drink stock, as well as listing income such as food/drink sales.

Next, we can start entering financial values for each budget item and month. These figures may be based on previous accounts you’ve kept or they may be based on your best guess and available information. Now that you’ve entered those figures, it is best to format them so they are presented as currency.

To do this, simply highlight all the Cells you’ll be using to store or calculate financial values and then right-click. A menu of options will appear and here you need to select ‘Format Cells’. Another set of options will then appear and in here you just need to select ‘Currency’. If the ‘Symbol’ is not set to the currency you need, you can click on it and choose from a list of different currencies. Finally if you want to display negative numbers differently, there are a few options available.

Once you’re happy with your selections, click OK and the numeric values in the Cells you highlighted will change to present as currency, like so:

Now that you’ve entered and formatted the financial details for all your budget items, you can use the SUM Function to create a Formula that will calculate the monthly total for all your outgoings, like so:

Here you can see that instead of selecting a whole Column, the SUM is being calculated for certain Cells, and then by using the Fill Handle the Formula is repeated and used to work out the total outgoing for each month. You can then create a similar calculation to work out the total income for each month.

Finally, we can work out the Net amount for each month by writing a simple Formula to subtract the monthly outgoings total from the monthly income total, like this:

And there you have it – a simple budget using a combination of SUM and basic Formulas. You could take this further and apply what you’ve learned about SUMIF to work out which category of your budget costs the most in December and which category of your budget generates the most income in December. You could even make a Chart or Graph of your findings. Give it a try and good luck.

What’s next?

Once you fully understand these Excel basics, the next step would be to grow your Excel knowledge further by researching Pivot Tables, Data Validation and Functions like IF, SUMIFS and COUNTIFS. You can find all sorts of guides online by searching on Google or YouTube. Watch this space too, as we’re always releasing new guides on all sorts of topics to help you with your personal and professional growth.

Further Resources:

Most people try to limit their Excel exposure to the bare minimum (and even actively avoid it like that one weird relative we all have). But whether it be a project at work or just a personal budget, you need to know basic Excel to get things done right and done quickly. To help you get the most out of Excel, we’ve put together a batch of the best Excel tips for beginners.

Download your free Excel practice workbook

Follow along with this sample workbook.

1. Adding frequent actions to quick access toolbar

Looking around any version of Excel you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. Instead of switching between the different tabs of the ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar.

Microsoft offers several ways to make this happen but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar”. One of my personal favorites in my QAT is the Sort & Filter function as I usually work with a lot of data.

Before:

After:

Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar...” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.

2. Filtering data

Speaking of a lot of data, Microsoft has amazing tools to handle it all, which probably explains why it became the most important workplace computer program around the world. Excel’s basic function for handling all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a way that’s actually useful.

To do this, highlight all the data you want included in the filter (a quick way to do this is Ctrl + A). Then, click the funnel icon in the top left of the Home ribbon.

Now there will be a drop-down arrow at the top of each column in the filter. Here, you can select different values in the table and sort them. For example, let's say you wanted to see how many orders were made in a certain time frame. Just filter on the date column and select your desired time frame.

From our example, you can quickly see there were only two orders in September 2014.

3. Adding dynamic header/footers

While it seems everything is going paperless, sometimes there’s still a need to print. One of the best ways to keep track of what's printed out of Excel is by adding page numbers, timestamps and file paths to the header or footer. To make sure you don’t have to update these values every time you need to print a spreadsheet, you can add formulas that update automatically.

First, change your view of Excel so you can see the header and footer.

Next, add the following text to your header footer:

Before:

After:

4. Setting print areas on sheets

Now that you know how to automatically update what prints in the margin, another time-saving trick is set up print areas for your tabs to update what prints within the margins. If you like to keep all your work on one spreadsheet but only need to print out a certain portion, setting the print area is a great way to save time when you hit Ctrl + P.

To set your print area, highlight the cells you want printed. On the Page Layout ribbon, click the drop-down under Print Area and select Set Print Area.

In the same drop-down menu, there’s the option to clear the print area. This is useful if you’ve changed your spreadsheet and want to add more to printable area.

5. Paste Special settings

There are so many different ways to accomplish tasks in Excel. Consider the simple copy and paste functions. As if Ctrl + C and Ctrl + V didn’t make copying easy enough, Microsoft built Paste Special.

Here’s how it works. Say you want to copy and paste something but not everything like a just a number or format. Instead of copying everything and removing what you don’t want, you can use Paste Special. To do this, copy your information as usual, but instead of using Ctrl + V, right click and select Paste Special from the menu.

Some of the more popular Paste Special options include:

• Values - use this option if you only want to paste the text you see in the cells. It will leave all formatting unchanged.
• Formulas - Need to keep that formula but don’t need the formatting? Formulas allow you to do just that.
• Formats - Use this option to duplicate formats while leaving existing values and formulas.
• Column Widths - For when all your columns need to be a uniform width, this option saves a ton of time instead of adjusting them manually.

6. Group/ungroup columns to hide detail data

Spreadsheets with a bunch of complicated and detailed information can be hard to read and analyze. Luckily, Excel provides an easy way to collapse and expand the messy details to create a more compact and legible view.

Grouping in Excel works best for structured worksheets that have column headings, no blank rows or columns, and data is sorted by at least one column.

Select all the data you want to summarize in the group. Then, go to the Data tab > Subtotal. This will bring up a pop-up in which you can select how the data should be grouped and summarized. In the example below, we grouped by the change in OrderYear and summed on Total. This will show us total sales for each year and in total.

Here is the result:

Now with just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.

7. Protect sheets and workbook

When it comes time to send your Excel spreadsheet, it's important to protect the data that you're sharing. You might want to share your data, but that doesn't mean it should be changed by someone else. Luckily, Excel has built-in features to protect your spreadsheets.
To protect a sheet, click on the Review tab in the ribbon then Protect Sheet. This will bring up a pop-up box where you’ll add the unlock password and what functions users can still perform while the sheet is protected.

Once you click Ok, you are prompted to confirm the password and save the workbook. Now, if anyone tries to alter the information they will need that password. If you have a bunch of sheets you want protected, click on Protect Workbook follow the same procedure.

9. Trace precedent/dependent formulas

Have you ever used a worksheet that someone else set up? If you need to update the formulas and functions but do not know what other calculations will be affected you can spend a lot of time clicking around in the workbook with nothing to show for. Or you’re looking for a miscalculation and need to see where the data is flowing from.
Excel offers a simple way to review which cells are dependent upon others with Trace Precedents, and which cells contribute to others using Trace Dependents.

Both functions only work on the active cell selected so, unfortunately, it's one cell at a time. Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab to generate the blue arrows. Data flow follows the direction of the arrow with the blue dot being the precedent and the arrow being the dependent.

Below, Trace Dependents for cell E2 shows it only flows to cell G2.

And Trace Precedents for cell I4 shows cells E2 and F2 are the only cells flowing to it.

These functions work across tabs of the same workbook and different workbooks with one exception. Trace Dependents won’t work for external links for workbooks unless they are open.

9. Data validation for drop-down menus in cells

Using the drop-down list is a great way to impress your co-workers and boss with your Excel skills. At the same time, it’s a very user-friendly way to make sure custom Excel sheets function properly.

Excel For Beginners Powerpoint

This tool is used to enter data in a spreadsheet from a predefined list of criteria. The main purpose of using drop-down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.

Spreadsheets For Beginners

This also gives you the possibility of controlling exactly what can be entered into a cell. This makes it perfect at validating inputs. First Select Data on the Ribbon and the Data Validation.

Next, select your parameters. Below we used the months of the year to populate OrderMonth.

Once you click Ok, click on the drop-down arrow next to the cell and select from the list.

Note: now that one cell is set up, it can be copy and pasted in the remaining cells below.

10. Text-to-column - splitting data in one cell to multiple cells

Have you seen data in excel that you need but there’s other information in those cells that won’t work with your formulas? While there are some complex formulas that will help you split your text into new columns, it can consume a lot of valuable time. The speedy way to split this up is Text to Columns which splits all the selected cells at exactly the same point and puts the results in separate columns.

You can use Text to Columns in two different modes: fixed-width and delimited. Fixed-width is useful if can separate your data using a straight line through all the rows while delimited splits the text based upon the text such as every comma, tab, or space.

For example, let’s look at a delimited Text to Columns to remove cents from our total column.

Like most functions on the Data Ribbon, first, highlight your data and then click Text to Columns. Once here you’ll choose between fixed-width or delimited.

On the next screen, set your splitting criteria. In our example, we used the period.

On the final screen, you have the option to exclude sections and change the formatting. This will save you crucial steps later. Then click Finish.

And finally the results! All of the cents were placed in the column just to the right.

11. Creating simple graphs

One of the best ways to improve your Excel skills is being able to communicate your results to others. Graphics, images, and charts are great ways to visualize and represent your data to end users, and Excel does a great job of automating this process. Let’s take a look at how to set up a simple chart using our sample sales data.

A simple graph starts with two sets of data, your independent (date) and dependent (results) variables. Below we have selected OrderDate and Total.

Clicking on Insert in the Ribbon you’ll see all the graphing options. For this example, a simple line graph will show us the total sales for each date.

Excel makes graphing so simple the results even include the title! So the next time you’re called into a last minute meeting you can create a simple graph on the fly in the meeting.

Bonus: Check out this lesson on how to create column charts in Excel:

Conclusion

Now that you’ve learned more about Excel it's time to put that knowledge into action. These helpful tricks will make your work so much faster you’ll wish you knew about them so much sooner.

For more helpful Excel tips for beginners, check out our articles on the best Excel hacks, Excel essentials, and the best Excel shortcuts. You can even take our free crash course to quickly cover some Excel basics.

Want to improve your Excel skills even more? Don’t be the last person in your office to learn Excel - sign up for the comprehensive GoSkills Basic Excel course today!

Ready to start learning? Become an Excel ninja today with GoSkills

Level up your Excel skills

Free Excel Training For Beginners

Become a certified Excel ninja with GoSkills bite-sized courses

Excel For Beginners Step By Step