It's difficult to know which finance books are truly worth reading because there are so many different titles to choose from. Whatever your level of expertise, you'll almost certainly discover something on this list that will help you improve your financial literacy. We've covered personal finance, investing, economic books, and business books. Here are our 39 top recommendations:

- The Intelligent Investor
- The Personal MBA
- The Simple Path To Wealth
- Think and Grow Rich
- Rich Dad Poor Dad
- The Psychology Of Money
- The 48 Laws Of Power
- The Millionaire Fastlane
- Blue Ocean Strategy
- Built To Last
- The Big Short
- I Will Teach You To Be Rich
- Delivering Happiness
- Deep Work
- Principles
- One Up On Wall Street
- Outliers
- Profit First
- The Alchemy Of Finance
- Zero To One
- 7 Habits Of Highly Effective People
- Hooked
- First 90 Days
- The Chimp Paradox
- The Monk Who Sold His Ferrari
- Happy Sexy Millionaire
- Start with Why
- How To Own The World
- The New Confessions Of An Economic Hitman
- Your Money or Your Life
- The Millionaire Next Door
- Broke Millennial
- The Total Money Makeover
- Debt Free Degree
- The Automatic Millionaire
- Financial Freedom
- The Richest Man in Babylon
- Spend Well, Live Rich
- A Random Walk Down Wall Street

*When you buy through links on our site, we may receive an affiliate commission. As an Amazon Associate, we earn from qualifying purchases.*

- Find value in the stocks you are buying
- Don't get caught up in hype or the latest financial products
- Invest for the long term, fees will destroy income unless you are are a professional.

The Intelligent Investor: The Definitive Book on Value Investing by Benjamin Graham and Jason Zweig is widely considered to be one of the best finance books of all time. Graham was a highly respected investor and economist, and in this book he lays out his investing philosophy that centers around finding value in companies.

This book is perfect for those who are looking to learn more about how to value stocks and other investments. Warren Buffett once said that The Intelligent Investor was "the best book on investing ever written."

Josh Kaufman provides an overview of the most important concepts taught in business school, and helps readers learn these concepts on their own.

Kaufman begins by explaining why traditional business school is a bad investment for most people. He argues that the cost of business school is too high, and the return on investment is often negative. He also argues that most business schools do not teach practical skills that can be immediately applied in the real world.

Kaufman then provides an overview of the core concepts taught in business school. He covers topics such as accounting, finance, marketing, and strategy. He also provides an overview of the different types of businesses, and explains how businesses operate.

Collins provides readers with a clear and concise guide to achieving financial independence and building wealth. JL Collins shares his own story of how he achieved financial freedom, and provides readers with actionable steps they can take to achieve similar success.

The central theme of Think and Grow Rich is that personal wealth can be created through one's thoughts and actions. Napoleon Hill explores methods to accumulate money, how to keep it, and how to use it. Many of the book's 15 chapters cover principles for creating wealth and lays out strategies that may help you achieve it.

Robert Kiyosaki explores the differing financial philosophies of his two fathers (one of them his best friend's father) - one rich, one poor. His friend's father is the one that is very rich.

Kiyosaki argues that the biggest difference between the rich and the poor is not income, but what you do with your money. He advocates for a shift in thinking in order to achieve financial freedom.

Morgan Housel starts by exploring how our brains are wired when it comes to money. He looks at how we make decisions about money, and how those decisions are often based on emotions rather than logic. He goes on to discuss how our beliefs about money can lead us to make poor financial decisions.

Robert Greene dives into the psychology of power and how it can be used to achieve success in life. The book is broken down into 48 laws, each of which is designed to give you a better understanding of how power works, how it can be used to your advantage, and prevent other people from using these tactics on you.

MJ DeMarco shares his secrets on how to make money and starts out by discussing the difference between the "fastlane" and the "slowlane." The fastlane is the path to wealth, while the slowlane is the path most people take. He also discusses building a business that can make you wealthy, rather than working for someone else.

W. Chan Kim and Roger Wayne present a systematic approach to find "blue oceans" of uncontested market space and make the competition irrelevant.

The key idea behind blue ocean strategy is that companies should focus on creating new markets rather than competing in existing ones. The authors argue that the best way to do this is to find uncontested market space, where there is no competition. This can be done by looking for areas where there is unmet customer need, or by creating entirely new markets.

Jim Collins and Jerry Porras take a comprehensive look at some of the world's most successful companies. These organizations have not only survived but thrived over long periods of time, through good times and bad. It explores what sets them apart.

The Big Short is a 2010 book by Michael Lewis, based on the true story of four outsider investors who predicted the credit and housing bubble collapse of the mid-2000s. These men saw what no one else recognized—that the U.S. economy was headed for disaster. They bet against the American mortgage market, making a fortune in the process.

Ramit Sethi shares his approach to money and life. He provides actionable advice on how to save money, make more money, and live a rich life. He also shares his unique approach to life in a way that is full of experiences and happiness, not just material possessions.

This book is both written by, and about Tony Hsieh and his journey to create Zappos, an online retail company that sells shoes, clothing, and other accessories. Hsieh shares his story of how he started the company and how he has grown it into a successful business. He also discusses the importance of culture and customer service within a company, and how they contribute to delivering happiness.

In his book, Cal Newport explains what deep work is, why it's important, and how you can achieve it. Deep work is the ability to focus without distraction on a cognitively demanding task. It is a skill that allows you to quickly master difficult material and produce better results in less time. Newport argues that deep work is becoming increasingly rare as the world moves more and more towards a shallow, distractible culture. He believes that those who cultivate a deep work ethic will be the ones who succeed in our increasingly competitive economy.

Ray Dalio shares his unique approach to life and work, which he has developed over four decades as founder of one of the worlds largest investment firms. Dalio believes that much of life and business can be distilled into rules.

Peter Lynch argues that the key to outperforming the market is to find good companies and then hold on to them for the long term. He also believes that investors should pay attention to their own personal experiences when making investment decisions.

In Outliers, Malcolm Gladwell examines the lives of successful people in order to understand what makes them different from the rest of us. He looks at how they were raised, their education, and the opportunities they had available to them.

Gladwell argues that success is not simply a matter of talent or hard work. It is also the result of being in the right place at the right time and having the right opportunities. He gives the example of Bill Gates, who became a successful computer programmer because he had access to a computer in his school.

In conclusion, Gladwell argues that we should not be quick to judge people based on their success or failure. Instead, we should try to understand the factors that contributed to their success or failure.

Mike Michalowicz provides a new perspective on how to try and ensure your business is profitable. Rather than looking at profitability as an after-the-fact measure, Michalowicz shows that it should be the primary focus of every business decision you make.

George Soros shares his insights on a wide range of topics, from the role of central banks to the importance of managing risk. He also provides an in-depth look at his own investing philosophy and how he has applied it to his career.

Peter Thiel starts by talking about how most people think about progress in the wrong way. They see it as a linear process, where each new thing is just a slightly improved version of what came before. But in reality, progress is more like a tree: it happens when someone branches off from the existing world and does something new.

To build something new, you have to think differently from everyone else. You have to be able to see the world in a new way and identify problems that no one else has noticed. This is why originality is so important.

Stephen Covey outlines seven key habits that are essential for success in life. They cover everything from taking initiative and being proactive to communicating effectively and thinking win-win.

Hooked is a book about how to create products that people can't put down. In it, author Nir Eyal reveals the four-step process for creating products, which are designed to keep users coming back for more. They cover, trigger, action, reward, and investment and by using these four steps, it describes how you can create products that are designed to keep users coming back for more.

In The First 90 Days, Michael Watkins provides a framework for success during those crucial first three months in a new job. He offers advice on how to hit the ground running, build credibility, and make an impact quickly.

Watkins emphasizes the importance of understanding the organizational culture and the politics of the organization. He also stresses the need to create a network of allies and sponsors.

The book provides a step-by-step approach for those starting a new job, including how to assess the situation, develop a strategy, set priorities, and create a plan of action. Watkins also addresses common challenges such as dealing with resistance from others, managing your own emotions, and handling unexpected setbacks.

The Chimp Paradox is a self-help book that aims to help readers control their inner “chimp” or emotional mind, in order to live a more successful and happy life. The book is based on the idea that humans have two minds – the rational, logical “human” mind, and the emotional, impulsive “chimp” mind. The chimp mind is responsible for our survival instincts, but it can also lead us to make bad decisions that can ruin our lives. The book teaches readers how to control their inner chimp and make better choices in life.

The Monk Who Sold His Ferrari tells the story of Julian Mantle, a successful lawyer who decides to sell everything he owns and become a monk after having a life-changing epiphany. The book follows Julian as he enters the monastery and learns to live a simple, but fulfilling life. Along the way, Julian shares his wisdom with the reader, offering advice on how to live a happier and more meaningful life.

When Steven Bartlett was 18 he wrote down that he wanted to be a happy sexy millionaire. Bartlett's story is one of rags to riches. He was born in a working-class family in England, but he had a burning desire to be successful. He started his first business when he was just a teenager, and he quickly learned the ropes of entrepreneurship. By the time he was in his early twenties, he had already made his first business and has since gone on to build a massive business empire.

Simon Sinek provides a framework for building a successful business. The key is to start with the why – that is, the purpose, cause, or belief that drives everything you do. Your why should be more than just making money; it should be something that inspires others and makes them want to support your business. Many businesses start with what they do rather than why they do it, but the most successful businesses start from the ground up with their why.

Andrew Craig lays out his case for why everyone should aspire to achieve financial independence. He argues that financial independence is the key to true freedom, and that it's within everyone's reach if they're willing to put in the work. He then discusses building wealth, covering everything from investing in assets like real estate and stocks to starting your own business.

John Perkins was an economic hitman. His job was to convince developing countries to accept enormous loans for infrastructure projects that would ostensibly boost their economies, but which were really designed to further the interests of the U.S. government and multinational corporations. It is an eye-opening story of the way in which the American government and big business have conspired to foster a system that benefits only the rich and powerful.

Your Money Or Your Life explores the idea that we spend a huge amount of out time in jobs that exhaust us and don't spend enough time living. By tracking where our money goes and spending it in the same way we expend energy, we may conserve more for the times when it matters. Vicki Robin and Joe Dominguez also delve into the idea that if we are constantly pursuing bigger things without that additional work providing us greater fulfillment, maybe we should rethink out goals.

The Millionaire Next Door teaches you how simple spending and money management can result in a lifetime of wealth. It rejects the idea that you need an exorbitantly high salary to become rich and instead shows you how many millionaires accumulate their wealth by saving and living frugally. Thomas Stanley and William Danko, conducted years of research on millionaires in order to learn the habits that lead to their success.

Erin Lowry takes a deep dive into the world of money and how it affects young people. Lowry shows readers that they can take control of their finances at a young age and start living life without constantly worrying about money.

Dave Ramsey initially discusses the idea of saving $1000 as an emergency fund. He then moves into the Snowball method which is paying off your debts from smallest to largest. This section includes information on how to get rid of/reduce credit card debt, car loans, and mortgages. After getting out of debt, Dave Ramsay then talks about investing 15% of your income into retirement accounts. This book became a New York Times Bestseller.

Anthony O'Neal breaks down the college process and explains how parents can help their children graduate without debt.

Higher education is a huge investment and one that often comes with a lot of debt. In Debt-Free Degree, Anthony O'Neal offers a game plan for how parents can help their children graduate from college without taking on any debt. He covers scholarships, financial aid, and student loans and offers advice on how to avoid the most common mistakes parents make when it comes to paying for college.

David Bach starts out by telling us how he used to think that in order to become a millionaire, you either had to earn a lot of money or win the lottery. However, he soon realized that there are actually many people who become millionaires without necessarily having a high income. The key, he says, is to make your money work for you.

In the space of 5 years, Grant Sabatier went from having a few dollars in his bank account to over a million. He accomplished this through a combination of frugality, investing, and smart money decisions.

One of the most important lessons in the book is the importance of living below your means. This is something that Sabatier struggled with in his early 20s, but he was able to turn things around by making a concerted effort to save money.

First published in 1926, George S. Clason's classic book on investing has remained a staple on the shelves of many investors. The story centers on Arkad, a man who joins the ranks of the city's elite by following some simple investing principles, such as setting aside 10 percent of his income for savings. Clason's book is still relevant today, and its lessons can be applied to any number of investing scenarios.

Michelle Singletary's grandmother never had a high salary but she managed to raise a large family. Singletary takes us through the seven steps that her grandmother used to become financially secure.

Singletary's book is full of personal anecdotes and advice on how to save money and live a rich life. The book is divided into seven sections, each devoted to one of her grandmother's steps. Singletary's writing is clear and concise, and she provides plenty of helpful tips for those looking to get their finances in order.

In a time where it feels like those on Wall Street have better data and more resources than the average person, it would be easy to get discouraged about making your own investment decisions. After all, these people have made a career out of this and if they can't beat the market, how could you ever hope to?

Burton Malkiel, a Princeton economics professor, sets out to show us that investing isn't as complicated or difficult as it may seem. And in many ways, he succeeds.

There you have it, the 39 best finance books that we think will help you get a better understanding of the financial world live in. It was difficult to rank them in order, but we did our best. Do you think we missed any? If so, please let us know in the comments below.

]]>In this tutorial, we're going to show you three ways to find the difference between two columns in Google Sheets. Each method can be used for slightly different applications so if the first one doesn't work for you take a look at the next one. If you would like to follow along here is the example worksheet.

To find the difference between two columns in Google Sheets, you can use three techniques:

**Simple Formulas:**Subtract one column from the other using a basic formula like "=A1-B1."**"MINUS" Function:**Utilize the built-in "MINUS" function by typing "=MINUS(A1, B1)" for each corresponding cell.**Conditional Formatting:**Highlight the differences visually by setting up conditional formatting rules for the third column based on whether the value is greater or less than zero.

When we can keep things simple in Google Sheets it's often best to do so. simple formulas will allow others to quickly understand what is going on in the spreadsheet. In this method, we'll use a simple formula to calculate the differences between two columns.

**Create a Third Column:**Click on**Input the Formula:**In the first cell of the third column (C1), type the formula "=A1-B1". This formula calculates the difference by subtracting the value in cell B1 from that in cell A1.**Auto-fill the Formula:**After entering the formula in C1, click on the small blue circle at the bottom right corner of the cell (the fill handle) and drag it down to apply the formula to the entire column. Google Sheets will automatically adjust the cell references for each row.

Voilà! Column C will now show the differences between your two original columns. If you see a positive number in Column C, it means that the value in Column A is greater. Conversely, if it's negative, Column B is greater.

Google Sheets also provides a built-in function for finding the difference between two values – the "MINUS" function. Here's how to use it:

**Create a Third Column:**As before, click on a new column (e.g., column C) where you want the differences to appear.**Input the Formula:**In the first cell of the third column (C1), type "=MINUS(A1, B1)". This function subtracts the value in cell B1 from the value in cell A1.**Auto-fill the Formula:**Use the fill handle to drag the formula down the column, just like in Method 1.

If you want a more visual representation of the differences between two columns, you can use conditional formatting. Here we are going to combine the MINUS function with conditional formatting.

**Use the MINUS function:**Follow the steps in method 2 to show the differences between the two columns.**Select the Third Column:**Highlight the entire third column (e.g., column C).**Applying Conditional Formatting:**Head to the top menu and click on "Format," then choose "Conditional formatting."**Set Up the Rules:**In the conditional formatting pane, select "Format cells if," and choose "Less than" or "Greater than," depending on your preference. Then, enter "0" in the next field.**Choose a Format:**Click on the paint bucket icon to select the formatting style you want for cells where the condition is met. You can tweak font colors, and backgrounds, or add other formatting options to make the differences stand out.

Now, Google Sheets will visually highlight cells in column C where the value is less than or greater than zero, indicating the differences between the two columns.

In conclusion, mastering the art of finding the difference between two columns in Google Sheets is a fundamental skill that can unlock insights within your data. Whether you're crunching numbers to compare sales figures, monitoring inventory fluctuations, or scrutinizing survey responses, the ability to calculate these differences is a valuable skill to have.

]]>Here is a link to the free Habit Tracker, to make your own copy simply click "File" > "Make a copy". It has been built to help you track up to 15 habits at a time. When you mark a habit completed the line chart will also update to show you a holistic view of how you're keeping up with your habits.

You can make a copy of the template and get started right away but it’s worth understanding how the sheet works so that you can modify it slightly to tailor it to your needs. I'm going to show you how to build a habit tracker that will enable you to monitor whether you’re keeping up with your good intentions. There is a graph at the top of the sheet that will show you what percentage of your habits you’ve managed to keep up with for a given day and show you how that compares with the rest of the month.

We will also break down habits into four categories, health, finances, spiritual, social. Spiritual habits can be meditation, yoga, or journaling. And social habits can be as simple as calling a friend or going on a coffee catch-up. We will also create a doughnut chart about what type of habit you’re prioritizing. Each month you only have to duplicate the tab with the tracker on it and update the name of the month and the rest of the sheet will automatically update.

The first we’re going to do is open up a new spreadsheet in Google Sheets and go to cell A1. We’re going to create a margin for our template by reducing the size of column A before adding our heading to the first two rows. Go down into cell B1 and type Daily Habit Tracker. We are then going to merge cells B1 all the way up to AN1. Change the font to EB Garamond and increase the font size to 25.

We are now going to add an optional tagline below the sheet title by going to cell B2 and typing “Your Habits will determine your Future”. Similarly, to how we merge the title we’re going to merge cells B2 to AN2, adjust the font to EB Garamond, and the font size to 18. We’re then going to add a background color from A1 to AN2 and for this, we're going to use one for the built-in colors, it’s called light red berry 3, adjust the text color to dark grey 3 and center the text.

We’re next going to merge cells B4 to B6 and enlarge the column slightly. Input the first day of the month that you want the tracker to start on. For this example, we are going to use October 1st 2023. Highlight the cell, go to the format number, custom date, and time at the bottom, and then select the month and year format. We're then going to remove the comma and one of the spaces before clicking apply so now only the month and the year will show.

We are then going to match the font color to the color we've been using in the title and center vertically and horizontally and adjust the font size to 21. We're then going to add alter borders but before we do that we're going to add the third option into the dropdown we are then going to change the color to the same color we use in our title. Click apply to alter borders and our sheet is starting to take shape.

We’re next going to add our dates. For this we’re going to use the sequence function, go down to H10 and type equals sequence. For our first parameter, the number of rows was going to enter the number 1. For our second parameter, we’re going to enter 31 because there are 31 days in October. For our third parameter, we’re going to use the date we have in our B4 which in our case is the start of the month, but if you prefer to start on a different date you can enter it in B4. Our final parameter will be 1 because this defines the step increment and we want to track each day.

Above the day of the month we’re going to add the day of the week and to do this we’re going to use the weekday function. So go into cell H9 and type equals weekday H10. adjust the font to EB Garamond and adjust the font color to light red berry 2. We want the day of the week to point up slightly so we’re going to go to text rotation and select tilt up. We can then drag this formula from H9 to AL9 and all of our days will populate.

Next, we’re going to add a few habits to our tracker. You can add your own but I'm going to add meditating, journaling, yoga, call a friend, morning walk, coffee catchup, budget tracker, spend report, finance blog, gym, and a few that we are going to add a placeholder for. We’re then going to go to H11. Then to adjust the color of the checkbox we’re going to go to text color and adjust it to light red berry 3. We can then copy this for the rest of the days of the month and for all of our habits.

We then want to track habit completion. So in cell F27, we’re going to add the word completed. in the row below we’re going to add not completed. And the row below we’re going to add the percentage completed. Now, we want to automatically calculate the percentage completed so in cell H27 we’re going to write equals countif H11 to H26, comma, TRUE. What this does is it looks at our range of habits and if we tick the checkbox it will add it to our total of our completed habit. Checkboxes have two states TRUE or FALSE, we’re TRUE is ticked and unticked is FALSE. This makes them easy to work with when using formulas.

Now for the number of not completed habits we’re going to use the same formula again at this time our criteria is going to be FALSE because we want to add up the boxes that have not been checked. And finally, for our percentage completed row we’re going to divide the total number of completed habit by the total number of habits, and in our case we will write equals H27 divided by H27 plus H28 in brackets. We can then drag these three new formulae from color H all the way along to column AL. Let’s check that our formulas update by checking a few of the boxes in our habit tracker and as we can see the formulae are updating so we know it’s counting correctly.

Now that we have tracked our habit completing percentage we can visualize it on a line graph. To do this we’re going to highlight our percentage completed row go to insert chart and select the smooth line chart. We’re then going to the customized chart in the editor, go down to series, and change the line color to light red berry 2. Next, we need to adjust the line chart and put it where we want it to show, it will also update as we tick off completed habits.

We’re next going to add a divider between our line chart and the checkboxes. To do this we’re going to highlight from A8 to AM8. To borders select the light red berry 3 color and check that we still have the third option selected for the thickness before adding a bottom border. We’re going to do the same on the B10 to B30 except this time we’re going to add a right border.

Everything is starting to take shape now. But the final thing we're going to add is a monthly overview of our habit type. This will allow us to track the type of habits we’re building over the course of the month. we’re going to create a separate tab called habit types and then add a few habit for different types of habits. For this example, we’re going to use spiritual, health, finance, and social. So on the right-hand side of our habit type, we’re going to add a summary table that shows the total number of each type of habit.

Next, go back into the tracker template and go to Insert Chart and under chart type, select donut chart, then under data range delete the current data range and click on the select data range option in the right hand side of the panel. We then going to highlight our summary table on the habit types tab. Now we need to remove the data labels.

The pie graph we’re going to add to our habit tracker is to look at how many tasks we’ve completed versus not completed throughout the month. In cell AM27 we’re going to sum the number of completed tasks and do the same in cell AM28 for not completed tasks. we’re then going to highlight both of those cells and go to insert chart and select the column chart, chart type. After adjusting the colors we are then able to visualize the number of incomplete tasks going down and complete tasks going up as we progress through the month.

So there it is. That’s one way of creating a habit tracker in Google Sheets. It’s the type I use to track new habits and try to add new ones to my schedule as other habits become part of my daily routine. But what works for me may not work for you. The good thing about Google Sheets is that everything we have done today is customizable. So if you don't like the color scheme and layout that we’ve used you can change it to one that you do like.

]]>Converting your Google Sheets document into a PDF is a quick process thanks to Google Sheets' user-friendly features. Here are two methods to achieve this, the first is the faster, the second allows for more customization.

**Open Your Spreadsheet:**First, open the Google Sheets document you intend to convert into a PDF.**Go to "File":**Locate the "File" option in the upper-left corner, and give it a click to unveil a dropdown menu.**Select "Download":**From the dropdown menu, hover over "Download," and another menu will appear.**Choose "PDF Document":**In the second menu, click on "PDF Document."**Name Your File:**A dialog box will appear, allowing you to name your PDF file. You can also choose the destination folder in your Google Drive. Click "Save," and your Google Sheets document will undergo conversion to PDF before being downloaded to your computer.- That's it! You now have a PDF version of your Google Sheets document ready for sharing and safekeeping.

If you prefer more control over the PDF settings, you can use Google Chrome's print feature to save your Google Sheets as a PDF. Here's how:

**Open Your Spreadsheet:**As before, open the Google Sheets document you want to save.**Go to "File" and Select "Print.":**Click on "File" located in the upper-left corner and opt for "Print" from the dropdown menu.**Choose "Save as PDF":**In the print dialog box that appears, under "Destination," select "Save as PDF."**Customize Settings:**You can customize settings such as paper size, orientation, and margins to your preference.**Click "Save":**Once you're satisfied with the settings, click the "Save" button, and will then be prompted to pick a location for saving your PDF file. Name it, select the destination, and click "Save."

Ta-da! You've successfully saved your Google Sheets document as a PDF using Google Chrome's print feature.

Let's understand why saving your Google Sheets as a PDF is a great idea. PDF, an acronym that represents Portable Document Format, is a universally accepted file format that maintains the formatting, fonts, and layout of your document across various devices and operating systems. Whether you're sharing a report with your team or submitting a project to a client, saving your Google Sheets as a PDF ensures that your hard work is presented exactly as you intended.

Saving your Google Sheets as a PDF is a simple yet powerful way to ensure your data is shared, viewed, and printed exactly as you intended. With just a few clicks, you can preserve formatting, maintain compatibility, and enhance security. So, the next time you're ready to share your Google Sheets masterpiece, consider saving it as a PDF for a professional touch.

If you had any issues exporting your Google Sheets as PDF's please let us know in the comments down below. Each week we cover new features of Google Sheets, if you have any areas you would like help with, feel free to send us a message.

]]>

Today we will guide you through the straight-forward process of removing cell borders.

Highlight the cells you would like to remove the borders from. Click on borders in the Google Sheets ribbon and select "clear borders".

Now to go into a little more detail on each step with an example. Let's say we have the following table and would like to remove the borders.

The first step in border-removal is selecting the cells from which you would like to remove borders. This is as simple as pointing your cursor, clicking on a cell, and dragging it to highlight the relevant cells.

For large spreadsheets where you want to highlight all of the cells in the sheet we can use the following keyboard shortcuts:

- '
*Ctrl + A*' for Windows - '
*Command + A*' on Mac

These keyboard shortcuts enable you to swiftly select all cells. And here's another tip! For whole row or column border removal needs, click directly on the row numbers or column letters respectively. e.g. click on 1, 2, 3 or A, B, C etc..

The toolbar provides shortcuts to many frequently used features. Located at the top of your Google Sheets window, it contains the borders 'Borders' button. It looks like a small grid and is located in between 'Fill Color' and 'Merge Cells.' Click this icon, and you'll find 12 sub-options related to borders.

Once you click on 'Borders,' select 'Clear Borders' button to remove the borders. It's usually represented by an empty grid made up of dotted lines.

The icon should be located in the bottom-right of the menu of options before 'Border Style.' Go ahead and click on it. At that moment, the borders of the cells you have highlighted will disappear.

Borders are useful for adding structure and clarity to a spreadsheet. But if you've ever received a Google Sheet from someone else or maybe have gone a little overboard with bordering yourself, you'll understand how too many borders can create unwanted visual noise.

Excessive usage of cell borders can lead to distraction, which is the exact opposite of what we generally intend when adding them. They have the potential to make your spreadsheet look chaotic and over-complicated. This 'border chaos' can also make it more difficult to read information on your sheet, causing confusion for everyone using it.

Moreover, excess borders can cause formatting problems when printing documents or converting them into PDFs. Clarity is key in any data presentation, and too many borders are something that can have the opposite effect.

So there you have it... Three easy steps to sweep clean the borders in Google Sheets while keeping everything else intact. Remember, if you ever want them back for whatever reason - simply do this dance backward by adding borders instead of clearing them! You can even choose which border style, color, and thickness suits your sheet.

Maintaining good formatting practices, including border elements contributes to a cleaner, more efficient spreadsheet experience. If you have any questions about removing borders, do not hesitate to leave a comment down below.

]]>Whether you're a newbie still learning to sort columns or an expert data modeler there is a course for you. With the right training, you can go from never having used the platform to the "go-to" person in the office for sheets in a relatively short amount of time.

In this article, we'll take a look at the top courses on offer that will turn you into a Google Sheets master. From beginner basics to advanced analytics, these classes will unlock the full potential of Google Sheets for your school, work, or personal projects. We compiled this list by carefully evaluating numerous courses based on factors like instructor expertise, content quality, skill level, and real user reviews. Let's get to the list!

Claiming the top spot on our list of the 10 best Google Sheets courses is of course Classical Finance's Google Sheets Mastery. We may be a little biased here as it is a course we created but it lives up to its name, providing comprehensive training to master Google Sheets on an advanced level that can transform your career.

While other courses only scratch the surface, Classical Finance dives deep into practical, real-world applications of Google Sheets. Through hands-on projects and dynamic model building, you'll gain career-elevating skills to analyze, visualize, and automate data like never before.

Classical Finance's unparalleled expertise shines through in the carefully crafted curriculum. The extensive curriculum maps out an in-depth education that no other Google Sheets course can match. Learn to create stunning interactive dashboards that communicate insights clearly to stakeholders. Streamline workflows and scale productivity by automating repetitive spreadsheet tasks. Tackle complex data modeling and analysis with ease.

Classical Finance's commitment to effective practical learning ensures you walk away with skills to grow your career, not just theoretical knowledge. You'll stay on the cutting edge of Google Sheets capabilities. If you're serious about maximizing your Google Sheets skills, then look no further. The deep training and career-focused approach make this the top choice for professionals wanting to unlock the full potential of Google Sheets.

Landing at #2 is Coursera's Doing More with Google Sheets. This beginner-friendly course helps you organize, analyze, and share data with ease through step-by-step video lessons. The curriculum walks you through spreadsheet basics like formatting cells, using formulas, and collaborating seamlessly using Google Sheets' sharing features.

With five modules and a companion spreadsheet for hands-on practice, you'll gain the skills to create sophisticated spreadsheets that incorporate powerful functions and visualizations. Key topics include essential functions like data validation and pivot tables. You'll learn techniques to filter, sort, and analyze your datasets to uncover insights. Plus, discover how to visualize trends and relationships in your data with charts.

By the end of the course, you'll have the confidence to apply everything you've learned to your projects and share meaningful insights with your team. The intuitive, exercise-based approach makes this the perfect introduction for those new to spreadsheets. Become a spreadsheet power user without frustration thanks to the simple yet comprehensive training.

Coming in at #3 is edX's Analyzing Data with Excel. This introductory course is perfect for spreadsheet beginners looking to learn the fundamentals of data analysis using Excel or Google Sheets. With a focus on hands-on learning, the curriculum covers spreadsheet basics like sorting, filtering, and pivot tables through video lessons supplemented by practical labs.

You'll work with real-world datasets to gain first-hand experience importing, preparing, and analyzing data. Key topics include data quality, cleaning data, filtering, and sorting. The course builds your skills in performing calculations, applying formatting, and creating charts to uncover insights. You'll complete a final project analyzing a dataset from start to finish, cementing your new talents.

No prior experience is required! The course starts with spreadsheet basics, so anyone can follow along. Examples demonstrate techniques in both Excel and Google Sheets, making you comfortable with the most popular tools. By the end, you'll have the skills to go from data confusion to spreadsheet profusion. If you want a solid foundation in data analysis, this hands-on course is for you.

Coming in at #4 is Udemy's The Complete Google Sheets Course: Beginner to Advanced! This comprehensive course will turn you into a spreadsheet superstar through hands-on video tutorials spanning beginner to advanced skills. The extensive training curriculum covers everything from Google Sheets basics to advanced functions, formulas, pivot tables, data visualization, macros, and Apps Script.

You'll gain practical skills tailored to your needs, whether you're a student or a business professional. Real-world examples demonstrate how to use Google Sheets for tasks like data analysis, dynamic report creation, and data management. Retain knowledge through practical assignments that let you hone new abilities.

By the end, you'll master tools like queries and charts to wrangle, analyze, and visualize data. Lifetime access allows you to learn on your schedule. With the Complete Google Sheets Course, you'll go from spreadsheet novice to power user equipped with in-demand data wrangling skills for school, work, or personal projects.

Transform scattered data into powerful insights with the Complete Introduction to Google Sheets Pivot Tables. This comprehensive course focuses solely on mastering pivot tables, the ultimate tool for summarizing, analyzing, and presenting spreadsheet data. Through clear video tutorials and hands-on practice, you'll learn how to generate pivot tables in minutes to spot trends and patterns in your data.

The focused curriculum covers creating custom calculations and groupings that can be added to pivot tables, allowing you to dig deeper and gain new perspectives. You'll also learn conditional formatting techniques to highlight key information. Turn dry numbers into compelling graphs and charts that visualize the stories in your data.

With lifetime access, you can revisit materials to stay up-to-date. Say goodbye to spreadsheet confusion and unlock the real potential of your data with this invaluable crash course. The transformative pivot table techniques will equip any data analyst or spreadsheet user with the skills to derive insights from datasets large and small.

Master automated business reporting with the Master Reporting Automation with Google Sheets course from Udemy. Through advanced formulas and combinations, you’ll learn to build scalable systems that require no manual upkeep. Streamline financial modeling, forecasting, competitor tracking, and reporting tasks that you regularly repeat.

The hands-on curriculum covers techniques to construct files, dashboards, and templates that update themselves. You’ll also learn about professional spreadsheet design, effective data visualization, and tips for incorporating scripts. While this course assumes proficiency with Sheets, beginners can still benefit by putting in extra effort. Upon completion, you’ll have the skills to impress colleagues and boost productivity by minimizing manual work.

If you’re a financial analyst, controller, or business professional looking to maximize efficiency, this course will equip you with the spreadsheet mastery to achieve new levels of workflow automation.

Coming in at #7 is Skillshare’s Google Sheets for Beginners. If spreadsheets seem intimidating, this course breaks Google Sheets down into simple, bite-sized video lessons that are perfect for absolute beginners. The short videos walk you through the interface and essential features step-by-step in a way that's easy to follow and fun to learn. Hands-on exercises reinforce skills as you progress.

You'll go from data-phobic to spreadsheet fanatic as you master creating, formatting, and manipulating spreadsheets. The course covers entering data, using formulas and functions, formatting cells, managing rows and columns, creating charts, adding images, and more. Interactive projects introduce more advanced tools when you're ready. The foundation provided in this course will give you the confidence to continue growing your skills. Unlock the power of Google Sheets through this intuitive introduction tailored for spreadsheet novices.

At #8 is Udemy's Google Sheets for Entrepreneurs and Small Businesses. Get hands-on training to maximize Google Sheets and Drive for enhanced business productivity. Through building financial models and budgets from scratch, entrepreneurs will gain expertise in leveraging Sheets for data analysis, planning, and workflow optimization.

The practical curriculum covers spreadsheet basics like formulas, functions, and formatting. You'll learn techniques to structure, analyze, and visualize financial data with relevance for business operations. Build models to provide insights into budgets, forecasts, operational metrics, and more. Streamline collaboration and access with Google Drive integration. By the end of the course, you'll be equipped to deploy Google Sheets solutions that boost efficiency and performance for your startup or small business.

At #9 is LinkedIn Learning's Google Sheets: Advanced Formulas and Functions. Take your Google Sheets skills to new heights with this focused course on advanced formulas and functions. Learn tips and techniques used by the pros to summarize data, perform complex calculations, analyze financial information, and much more.

With insightful exercises and calm, precise instruction, concepts are easy to grasp. The hours pass quickly with this highly effective training from experts. While brief, this course packs a punch by cutting out fluff and diving right into the important stuff. In just over 3 hours, you can gain knowledge that would take months to learn on your own. Upgrade your spreadsheet skills to bring expert-level functionality within reach.

Coming in at #10 is LinkedIn Learning's Google Sheets Essential Training, the ultimate beginner course for learning the popular spreadsheet tool. Instructor Sally Norred provides a comprehensive introduction, starting with an intuitive tour of the interface. Engaging video lessons demonstrate how to enter data, use formulas and functions, collaborate with others, and tap into advanced features like conditional formatting.

With real-world examples and hands-on practice, you'll quickly grasp key concepts from formatting to data analysis. Well-paced tutorials ensure you have time to absorb each new skill before building on it. Whether you’re new to spreadsheets or converting from Excel, this course offers the ideal starting point. Come away with the knowledge to organize, share, and gain insights from your data. Detailed demos empower you to create functional sheets that deliver value. Earn a Certificate of Completion to showcase your new abilities.

There you have it – our picks for the top 10 Google Sheets courses to unlock your inner data ninja. With the help of these transformative classes, you'll go from spreadsheet novice to analysis aficionado, armed with career-boosting skills.

Whichever course you choose, be sure to apply your newfound talents right away. Consistent practice is crucial for building mastery. Turn theory into action by using Google Sheets for real projects, and watch your productivity and strategic insights soar.

]]>The IF function features in many of the advanced formulas written on Google Sheets. Today we will combine it with other functions to create IF CONTAINS functions that lets us SUM and COUNT number of cells IF they contain a specific text.

To create our IF CONTAINS function we will be using the IF, REGEXMATCH, COUNTIF and SUMIF functions.

If you would like to follow along with the examples we are going to work through, here is a link to the sheet. To make your own editable version, go to File > Make a copy.

Now onto the first example: We are going to create an IF CONTAINS function to determine whether each of the bakery items is a shortbread or not.

In cell B4 we can use the REGEXMATCH function to check whether the cell we're checking contains the text "Shortbread". The formula looks like this:

REGEXMATCH takes two parameters, text and regular expression. The text we are checking if there is a match for is in cell A4 and the regular expression we are checking against is "Shortbread" in cell B1. We have used an absolute reference for the regular expression so that is stays constant as we copy the formula down the list.

The combined formula looks for the matching text and returns "Yes" if it is there and "No" if it isn't. In our example data, both B6 and B6 return "Yes".

To count the number of cells that contain our string we can combine IF with the COUNT function. Here we will look for text matches and add them to a running counter if they do. The formula looks like this:

The COUNTIF function takes two parameters, and in our case the range that we want to test against the criterion is B4:B8 and the criterion is "Shortbread" in cell B1. The number 2 is returned because there are two shortbreads in the list.

If we have a list items with numeric values alongside it we can use the SUMIF function to add up how many of a particular item there are.

To add up the number of shortbreads sold, we need to input three parameters into the SUMIF function. The range to be tested against the criterion is A4:A8, B1 contains the criterion we're testing against and B4:B8 contains the range to be summed if a cell in the range matches the criterion. The formula looks like this:

So there it is, those are the fastest ways we can create IF CONTAINS functions to count and sum ranges if they contains particular values. If you had any trouble following the examples, be sure to check out the example sheet.

If you have any comments for us, or if you have any requests as to what we can cover in future articles, please leave them in the section below - we would love to hear from you.

]]>Check here for the example datasets that we use for the examples so that you can follow along in real time. And if you ever get confused or stuck, you can come back to them to remind yourself of how it all works.

Without further ado, let’s dive straight into the SUM function.

Unlike many other spreadsheet functions, the SUM function is both easy to use and easy to explain - **it will calculate the sum of a range of cells for you**. This can be rows, columns, or specific cells that you select. You can vary the range however you choose to, and the SUM function will return the total of every cell that you have included.

You can sum huge amounts of data without having to manually input each number which can save you a lot of time. Your data will also update ‘live’, so if you update a number within your SUM range, the totals will automatically update.

This can be particularly helpful when making projections and plans, or setting targets - you can play around with your data to see how varying figures will impact on the overall totals.

No matter how much data you want to use in this formula, there are only a couple of things that you need to remember.

The formula looks like this:

There are only really two components to it. First we have the **=SUM(**, this is basically signalling to Google Sheets what function we want to use, which in this case is the SUM function. Then we need to tell the function which cells we want to add up. This could be an entire row, column or a select group of cells. **That’s all there is to it. **

The best way to understand a formula is to run through some real world examples. So that's what we’re going to do:

For our first example we are going to look at some data from a fictional toiletry products supplier. They have a spreadsheet with each of their twelve products listed on a separate row in column A. And in row 1 they have the twelve months of the year. The data that they record is the total sales for each product in each month of the year.

You can probably guess where this is heading! While it’s great to have all of this data in one place, it would be far more helpful if we could have the totals - both for each month of the year, and for each product. So how can we do that?

Well, pretty easily! We can use the SUM function for our rows and our column to generate these totals and have everything there on one sheet. If you would like to follow what we are doing, just go to the ‘Example 1’ tab on the Google Sheet in the link below this video.

First off, we’ll sum the columns, a total for each month of the year. And naturally we’ll start with January. So let’s head to the cell B14 - in the column underneath the last product row to enter our formula.

We’ll start, as ever, with the equals sign (=), and then write SUM next to it, to let the Sheet know that we would like the total of the cells that we are about to select. Then we will need to open our brackets by holding shift and pressing the number ‘9’ on our keypad. Google Sheets might be ahead of us at this point, and suggest the range that we’ll want to use, but for this example we’re going to ignore that and do it ourselves, just to make sure we understand how it works.

So to enter the range we have two options - we can type in the cells that we want to use, or we can click on them with the mouse and it will be automatically filled into the formula.

If we were to type it in, we would write in the first cell that we want to use, which in this case is cell B2. Then, instead of having to type in every cell that we want to include, we can just type a colon (:) which in this context means ‘*every cell up to and including’* and then type in the last cell that we want to include, which in this case is cell B13.

Then we close our brackets (by holding shift and pressing ‘0’) and hit enter. Then our total will appear in the cell - the total amount taken by our company in January.

Now it’s time for another great time-saving feature on Google Sheets. Rather than typing the same formula over and over again for each of the months on the spreadsheet, we can just fill the one that we have just written across to all of the other columns, and it will be automatically adjusted to use the relevant cells for each month.

To do this, we just need to click and hold on the little blue circle in the bottom right of the cell, and then drag it across to the 11 other columns.

Then you will see the total for each month displayed in row 14, all the way across. It’s as easy as that.

We covered how to sum columns but what if we want to sum rows too, and how would we sum only certain values from each row? In this example we are going to find the total sales for each product over the whole year - and to get this we will need to add up each month. Fortunately, we now know how to do this!

In the last example, I said that there were two ways to enter the cell range, one by typing, one by using the mouse, so for this example, we will use the mouse so that you can get comfortable using both methods. Don’t worry though, this is just as easy, if not easier.

So let’s add in an extra column to the right of our data, which we’ll give the heading ‘Total’.

Then in the first row of data, row 2, we’ll add in our formula. So let’s go to cell N2, and make a start.

We’ll first type in the equals sign, and then write the word SUM as we did before, before opening up the brackets. Now, to enter the cells that we want to include, rather than typing them out let’s click on them. Not only can this be easier, but you are also less likely to make a mistake - typos when writing formulas are a frequent cause of errors and this more visual method can help you avoid them.

So, with the brackets open, we can first click and hold on the first cell, which just so happens to be cell B2 again, and then drag that all the way along the row to the final cell that we want to include, which is cell M2. You will see now that the text B2:M2 has been automatically filled into our formula, so all we need to do is hit enter and our total figure for Shampoo sales is entered into cell N2.

And now we know how to fill this formula into all of the other rows - we’ll just click and hold onto the small blue circle in the bottom right of the cell again, and drag it all the way down to cell N14 - this will then include the monthly totals that we made in the first example, and therefore giving us the overall sales figure for that year.

And there we go, we have now used the Sum function on our column and rows to give us totals.

There is one final thing that you might need to consider when using the SUM function - what if you want a total for just a few sections of a column or row, that doesn’t run in one complete block. To use an example from the data we have been using, what if you would like a quick year total for just a few of the products? Say, Soap, Face Wash and Bubble Bath?

Well, we just need to alter the way we select our range - everything else stays the same. We still start off with =SUM and open our brackets. But then we need to select just the cells that include the data we need - so in this case it would be N4, N10 and N13.

If we are typing in our range, we will type in each cell, separating them with a comma - so (N4, N10, N13), and hit enter. If we are using our mouse, we will **hold down control** and click on the three cells, which will automatically fill them into the formula, just like before.

Then we hit enter, and hey presto - we have our total for that specific group.

Thank you for reading this guide, we hope that you have found it helpful. As we said at the start of the article, the SUM function is the most used on Google Sheets, so it is so important that you are comfortable and confident using it. It’s the first step to really making the most of what Google Sheets has to offer.

If you have any comments for us, or if you have any requests as to what we can cover in future articles, please leave them in the section below - we would love to hear from you.

]]>First of all we are going to run through what VLOOKUP is used for, and how it might be able to help you, and then we are going to run through a couple of examples so that you can see, step by step, exactly how this function works and how you can apply it to your own data.

You can follow along too. Click on this link to find the datasets that we have used for these examples, meaning you can check back on them at any point if you need a reminder of how it all works.

Learn The Sheets Skills Used By Fortune 500 Companies In 30 Days

Okay, now it’s time to get started.

VLOOKUP actually stands for “Vertical Lookup” - it’s a standard function within Google Sheets that scours your spreadsheet vertically to find a specific piece of data. Or to put it in a simpler way, it’s a formula which can find the information you are looking for - it will scan to find the information which can then be cross-referenced or displayed with some other data.

Using VLOOKUP, you can find that information immediately, without having to manually scan through all of the data - so it can save you a lot of time.

It can seem quite a daunting function to use, though, as when you first look at the function it can seem a bit complicated. There is a set syntax that is required for the function to work that you need to understand before you can use it, but it is much simpler than it looks.

Here is the syntax:

Now let’s break that down piece by piece. The first parameter is the **Search_key**. This refers to the value that you are searching for - so for example, if you were searching for a particular order number, you enter the cell that contains that information here.

After that, we enter the **range** - this is where we are going to be looking for the data, which will be two or more columns. The first column will always be the place where it looks for your **Search_key**.

Then we are on to **index**, which is the column number that we want to extract data from. So, for example, if you wanted the order value of each of the orders that you selected in the **Search-key** section, and this figure was in the next column, you would enter 2, as the information you want is in the second column.

Finally, we have **[is-sorted]**. This has to be either True or False, but most of the time it will be False. You would only enter True if the first column in **Range** is sorted in ascending order - this would return the *closest match* to your **Search_key**. If you, as you will in most cases, enter False here, the formula will only look for the *exact match* from your **Search_key**.

If you are struggling a little bit to take all that in, don’t worry - it will all start to fall into place now as we run through a couple of examples using our fictional commercial bakery. You can follow exactly what we are doing by clicking on the link in the section below.

It’s a stressful day in the office of our bakery as one of their customers, Asda, have been in touch to say that they haven’t received a number of orders that they have placed. They have provided a list of order numbers, so our team needs to get to the bottom of it and find out when each of them were shipped, so that they can speak to the, usually reliable, courier company. Here is the data.

Going through the huge list of orders would take too long, so they decide to do a VLOOKUP to get the date that each of these orders were shipped. Let’s help them out.

First of all, we are going to create a new sheet, named ‘Missing Orders’, so that we keep all the information in a neat, clear place. VLOOKUP works between different sheets. The first step is to enter each of the orders that our customer has claimed are missing in the first column, and we’ll add a Status column to mark them all as Not Received. Now we need to add a column for the Date that they were shipped, and create a VLOOKUP to enter that data.

To do this, first we’ll add =VLOOKUP into cell C3, and open brackets to activate the function.

The **Search_key **in this instance is the order number - that’s the value that we need to look up - we need information that is attributed to this order number. That information is in cell A2 on this sheet, so we can either type in ‘A2’, or just hold down Control and click on the cell we want to use.

Next, we need the **range **of cells that hold the information that we need. For that, we can head over to the ‘Bakery Sales’ sheet and select each column, while holding Control. We only really need up to the Date Shipped Column, but it doesn’t hurt to select them all. As long as the Order Number column is the first one, that’s all that matters.

Next, we need to ender the **index**, the column that we need the information from - in this case the Date Shipped column. That is the fourth column along, so we just need to type the number 4 here.

Finally, the data isn’t sorted, so we’ll add FALSE at the end of our formula. Now our formula reads: =vlookup(A2,'Bakery Sales'!A:F,4,false)

Then we hit enter - all of a sudden, the date the order was shipped will appear in the correct cell. To fill this formula down the column, we just need to drag the square in the bottom right of the cell, and it will automatically fill it where we need it. We now have all the data we need for an argument with the courier company!

For our second example, let’s imagine our bakery company wants to add a bit more detail to their spreadsheet. They have the quantity of each order, but not the value. Rather than going down and working each one out, for each individual product, they can do a VLOOKUP to automatically work out the value of each order, based on the price of the product that has been ordered.

To illustrate how to do this, we have created a new sheet ‘With Product Prices’, but they wouldn’t need to do this, they could just add it on to the existing sheet.

As you can see, we have added a small table with the prices of each product next to our main data - this is going to be our **range** that we are going to use to add information into our spreadsheet.

We first have two additional columns - first a column that will display the Unit Price, and then one with the order value. Now we need to populate the Unit Price column with a VLOOKUP formula.

So we’ll start with =VLOOKUP and open our brackets. Then we need to select our **Search_key**, which for this one will be our Product - that's the key bit of information for which we need to get the price, so we’ll hold Ctrl** **and select cell B2.

Next, we need the **range**, so we’ll hold Control again and select all the data in our little table at the side (cells J3:K9). Then we’ll enter the number 2, as we want the information in the second column of our range - the Unit Price. Finally, we just need to finish with **FALSE** and hit enter - our Unit Price will appear. Make sure to use absolute values for the range so it doesn't change as the formula is copied.

From here, we’re on easy street. We just fill down so the formula goes down to the bottom of our data, and to fill the Order Value column, we need a simple formula - =F2*E2 (or Quantity Purchased multiplied by Unit Price) to give us the over value. Fill that one to the bottom, and we have got the value of every order in a matter of seconds. It’s as easy as that.

As you can see, using the VLOOKUP formula is in no way as complicated as it might first look. Once you break it down into each section, it really is quite straightforward. There are few things that you should consider though:

Firstly, if the formula goes wrong and shows up the ‘#VALUE!’ error, first check to make sure that you have **[is_sorted] **as** FALSE**. Another common mistake is forgetting to enter the Index number - which column within your range that you want to extract the data from.

Another thing to note is that on Google Sheets, the VLOOKUP formula is case *in*sensitive - so it doesn’t distinguish between upper and lower case characters. Something to bear in mind particularly if you use both upper case and lower case for order numbers, for example.

When you hold control and click on the cells that you want to use in the formula, the commas (which act as delimiters) will automatically be added by Google Sheets - but if you find you have an error, go back and check that the commas are separating each element of the formula.

Finally, if you get a #N/A error, double-check your search range as this may be where you have an error - maybe you have added a column since you created the formula which has sent it all out of sync?

We hope that we have given you everything you need to start adding VLOOKUP formulas to your data in order to save yourself a lot of time and hassle. There are so many possibilities when you know how to use VLOOKUP, we are sure you will find plenty of opportunities to use this new skill!

Please leave any comments below, especially if you have any areas of Google Sheets that you would like us to cover in the future.

]]>Here is how you sum a filtered list in Google Sheets:

- Type
**=SUBTOTAL(**into the cell you wish to display the sum in. - Use 109 for the first parameter followed by a comma and then input the range of the filtered list e.g.
**=SUBTOTAL(109, A1:A10)** - Hit enter to reveal the total.

The SUBTOTAL function takes a function_code as its first parameter. Entering the number 9 tells Google Sheets to **sum** the values in the specified range. Then, **prepending** it with the number 10 lets Google Sheets know to skip the hidden cells. Click here for a working example.

The SUBTOTAL function calculates subtotals from vertical lists. Its first parameter, the *function_code *can be adjusted to change the way it aggregates.

- 1 for AVERAGE
- 2 for COUNT
- 3 for COUNTA
- 4 for MAX
- 5 for MIN
- 6 for PRODUCT
- 7 for STDEV
- 8 for STDEVP
- 9 for SUM
- 10 for VAR
- 11 for VARP

To avoid hidden cells the number 10 is prepended to this *function_code. *Additional cell ranges can be incorporated by adding them after the initial one e.g. =SUBTOTAL(function_code, range1, range2...).

Notes:

- When an autofilter is applied, the cells filtered out are not included in the SUBTOTAL regardless of the
*function_code*. - The SUBTOTAL function will also filter our other SUBTOTAL functions within the range specified. This is to prevent double-counting e.g. if the function is being applied to the entirety of column B, and column B already contains SUBTOTAL functions, those cells will not be included in the new SUBTOTAL.

The example here contains the financials for a profitable (but fictional) company. The boss has *hidden rows 7 and 8* and wants subtotals calculated in row 14. Now it would be possible in this *particular* example to do it manually but we're going to use the SUBTOTAL function instead.

To do this we need two parameters, *function_code* and *range. *We want the sum so we're going to use 9 and because we want to exclude hidden cells we will prepend it with 10, giving 109. The range for the first subtotal is B2:B13 so we will use that for our second parameter. Combining them together results in the following function: =SUBTOTAL(109, B2:B13).

The formula can then be dragged across to column F to calculate the totals for the other subtotals.

In the second example, we will look at subtotaling the same table but this time it has been filtered in a different way. The example spreadsheet is here if you would like to follow along.

The data has been filtered using the inbuilt autofilter in Google Sheets, and the months of February, July, and August have been removed. This autofilter means the SUBTOTAL function will automatically skip the filtered cells so we don't need to prepend the *function_code* with 10 this time. So we can proceed with the formula =SUBTOTAL(9, B2:B13).

In this case, using the *function_code *109 would have given the same result but it is worth noting why it can be omitted in this case.

The above explanation should cover the majority of use cases for subtotaling filtered data. If there is a concept that has not been covered that you are struggling with and would like help, leave a comment down below and we'll do our best to get back to you.

]]>There are two main ways to create exponent functions in Google Sheets. The quickest way to add an exponent in Google Sheets is to use the caret symbol (^) so we will go through that first. Links to the sample sheets are available in the examples section.

Here's the step-by-step guide:

- Type = into the cell you want to place the exponent formula in.
- Input the base number you will be using the exponent on.
- Hold the shift key and press 6 (on a regular keyboard) to insert the caret symbol (^) followed by your exponent (e.g. =2^3).

I rate this as the simplest way because it does not require learning any new functions and the exponent is created in the same way it is when writing normal math.

Here is how you use the POWER function for exponents:

- Type =POWER( into the cell you wish to work in.
- Input the base number followed by a comma and then insert the exponent.
- Close the bracket e.g. =POWER(2,6) and hit enter to see your result.

The first example will involve a straightforward scenario. Say we need to raise a base of 2 to the power of 4. There are two main ways of achieving the correct result and both examples are shown on the sheet here.

The first way of working out the result is by writing out the exponent notation: =2^4

Alternatively, we can use the POWER function. When using this function, remember that the base that comes first, followed by the exponent: =POWER(2,4)

Now let's say we have a list of numbers we want to raise to a given exponent. In this example, we will use cell references to raise the list of numbers to the same power and print the result into the cell next to the base.

There are two ways of achieving the desired result. The first is by writing out the exponent notation and the second is by using the POWER function.

For this example, we will store the exponent in cell D2. It would be long-winded to type out a unique function for every single number so we will create a formula that we can drag. In order to do this we need to use an absolute reference when typing in the exponent. That way when we drag the formula, the exponent stays constant.

Using exponent notation, we will reference the cell containing our base and use an absolute reference for the exponent value. For this example, the resulting formula is =A2^$D$2.

Using the POWER function, we again reference the cell containing our base and use the absolute reference for the exponent, resulting in the formula: =POWER(A2,$D$2).

Both of the resulting formulae can be dragged so there is no need to write them out again for the other bases.

If you would like to take a closer look at the workings behind this, the example sheet is here.

There are a huge number of use cases for exponents and I hope the examples above can be applied to your situation. If anything has been missed, drop a comment down below and we will try to add an example that covers the concept of the problem you're working on!

]]>Working with Google Sheets often requires data imports from other sheets. IMPORTRANGE is a helpful function enabling you to work across multiple sheets and share data between them. If you would like to try the examples for yourself, open the Google Sheets links, select "File" from the toolbar and select "Make a copy".

The IMPORTRANGE function has two required inputs, the spreadsheet_url and range_string.

- The
*spreadsheet_url*specifies the URL of the spreadsheet you intend to import data from. It must be enclosed within quotation marks or otherwise be a reference to another cell containing the spreadsheet URL. - The
*range_string*parameter selects the data to be imported.*Range_string*is composed of two elements, the sheet name and the range within that sheet to be imported. It is formatted

- Either the full URL or just the spreadsheet ID can be used for the
*spreadsheet_url*parameter.

- The first time you try to import from another sheet you will see a
*#REF!*error. You will need to press "Allow access". - If you do not own the spreadsheet you are trying to import data from you will receive the error, "
*You don't have permissions to access that spreadsheet*". If this happens you can go to the URL of the spreadsheet and request access. If the owner grants you access as an editor you will be able to import data using the IMPORTRANGE function.

IMPORTRANGE will automatically check for updates every hour while the sheet is open to ensure the data is up-to-date.

In the first example, IMPORTRANGE will be used to import a table from a source spreadsheet to a main spreadsheet. You can access the live source spreadsheet here and the sheet where the IMPORTRANGE function will be used is here.

On our source data sheet there are three key pieces of information we need to take a note of in order to correctly format our IMPORTRANGE function:

- The
**URL of the spreadsheet**, which is located in the address bar and highlighted below. - The
**range of the data**, in this case it is A1:C10. - The
**name of the tab**upon which the data is located, in this case it is "Sheet1".

Once we have these parameters we can return to our main spreadsheet to begin writing our IMPORTRANGE function. In this example we will use our spreadsheet ID, which we can use for the spreadsheet_url* *and our range string* Sheet1!A1:C10.*

Putting both parameters into the IMPORTRANGE formula we arrive at the function below:

Next, grant permission for the sheet to pull data from the source.

After a short loading period, the date from the source sheet will appear in the main sheet, as shown below. Adjusting the amount of data imported can be achieved by adjusting the *range_string *cell parameters.

**N.B.** In the above example we have just one tab on the sheet we are importing from, so by default it is the first (and only) sheet. In this example it is possible to omit the tab name in the *range_string *parameter. This is because Google Sheets defaults to the first tab when the tab name is left out of the query.

However, when working with data across multiple tabs, it is important to include the sheet name.

In many situations, data in the source sheet will need to be filtered and reordered when being imported. In our second example we will look at filtering the imported data using IMPORTRANGE multiple times.

The raw data this time has multiple additional columns.

The boss wants to take a closer look at advertising costs, office expenses, and client entertainment each month. He is not familiar with Google Sheets and has asked us to present it on another document.

The first way to achieve this filtered view is via using the IMPORTRANGE function three times which is the method we will demonstrate first. In this example, we will be using the same *spreadsheet_url* multiple times so **instead of using the full URL we will use a cell reference**. Our spreadsheet will be in cell E2.

The first time the sheets are connected we will again need to allow the main sheet to access the source sheet.

The data for the month is in column **A**, advertising costs in column **C**, office expenses in column **E** and client entertainment in column **F**.

To filter for just the month column, the formula will be:

Filtering for advertising costs will be the same, except we need to adjust the column being imported to column C.

We can combine the import for columns E and F as they are alongside each other in the source sheet.

The result is a filtered table with all of the data we need in the format we needed it in!

The intention of this guide was to demonstrate multiple ways in which the IMPORTRANGE function and its parameters can be used. However you need to import your data, I hope this guide has given you a foundational understanding that you can apply to your specific problem. If you have any questions, don't hesitate to drop a comment down below.

]]>The SUMIF function in Google Sheets is an enhancement of the basic SUM function. SUMIF calculates the ‘SUM’ of values in a range based on ‘IF’ they meet a specified condition, which is referred to as a ‘criterion.’

Common criterion examples are if a number is greater than (>), smaller than (<), or equal to (=) another number. Once a criterion has been established, the SUMIF function calculates the sum of the data points that meet the condition.

The syntax of the SUMIF function is as follows:

SUMIF has three variables:

**Range**(*required*): the range of cells that are to be evaluated by the criterion.**Criterion**(*required*): the specified condition to be met within the range. The criterion can be a number, date, text, cell reference, logical expression, or any function.**Sum_range**(*optional*): is an additional step for specifying a range in which to sum numbers. If you skip this step, the range used by the criterion will be calculated instead.

The SUMIF function is used in the same way in Microsoft Excel. If you’re looking to transfer files between the two programs, you do not need to worry about making edits when exporting this function from Google Sheets to Excel and vice versa.

Numeric conditions will calculate the sum of the numbers that fit within your range and criterion. Such a criterion can be whether a number is greater than (>), less than (<), and/or equal to (=) a specified number.

Our first example will take a range of numbers and look to sum the positive numbers.

Select the cell where you will input the formula (C2 in this example).

Input the function: =SUMIF(

Within the opening bracket, enter the first variable. This information will be the range of data that will be assessed by the SUMIF function in the final calculation. In this instance, it is A2:A7.

The second variable, which is the condition, will specify the numbers that will be calculated within the selected range. In this case, it is ">=0,” meaning the numbers that are greater than (>) or equal to (=) zero will be calculated. This will separate the positive numbers from the negative.

Press Enter to execute the formula and calculate the sum of the positive numbers, in this case, the result is 462.

SUMIF allows for date-based criteria too. In this example, we will sum the total number of sales after a given date.

Select the cell where you want to perform the function. In this example we will use D2.

Input the following formula to tell the function to only sum dates after 01/23/2030, =SUMIF(A2:A9,">"&DATE(2030,23,1),B2:B9).

Hit enter and the result will be calculated. In this example, the correct answer is 1461.

Text conditions can be used as a criterion to help you calculate a numeric total in relation to a text-based entry.

For example, if you have a list of people or items in one column, and numbers in a neighboring column, you can use specific text as the criterion. You can then add the optional “sum_range” to the formula. If you select the numeric data for this range, the numbers that are assigned to the relevant text will be used to calculate the final sum.

For this example, we will look at look at data for a clothing sale and use SUMIF to determine how many T-Shirts were sold.

Select the cell where you want to input the function (D2 in this example).

Type the following formula in the cell: =SUMIF(A2:A9,"T-Shirt",B2:B9)

- A2:A9 is the range of the criterion.
- T-Shirt is the text condition.
- B2:B9 is the ‘sum_range’ - the numerical range that will be added together based on the criterion for the total sum.

Press the Enter key to complete the formula. We can see that the total number of T-Shirts sold is 258.

SUMIF and ‘SUMIFS’ are similar functions: They both sum cells within a range based on whether they meet a specified criterion, but there is a slight difference.

If you want the convenience of SUMIF, but want to further refine your data, you can use SUMIFS for multiple criteria. The SUMIF function is limited to one criterion, while SUMIFS counts cells within a range that meet multiple conditions.

For example, if you have multiple columns of data in your document, you can have a criterion based on text, and then further refine it by adding an additional numerical criterion for the text-based cells to meet.

Learning about Google Sheet’s many functions can transform the way you use it.

One of the greatest benefits of the SUMIF functions is that it can save a massive amount of time, especially when handling a large volume of data. By being able to calculate sums based on specific criteria, you can get various pieces of data within seconds when you have a well-organized document.

The SUMIF function can also help you wrangle an incomprehensible set of data, whether it’s of a large volume, or has a diverse range of entries.

The ability to filter the data so you can get the sum of specific criteria means that you can make sense of a large quantity of data, by simplifying multiple entries into a more condensed format.

The SUM function is a basic method of calculating the sum of entire rows and columns, but is not feasible when you want the sum of specific values within a range.

Learning how to use the SUMIF function is essential when handling more advanced data in Google Sheets. The more advanced version of SUM can allow you to come up with totals for specific criteria and ranges, allowing you to understand, present and filter your data better.

Here, will go through some examples of how you can use the SUMIF function in Google Sheets. There are a number of types of criteria that you can use with SUMIF, and we will cover them here.

We hope this guide has helped you learn the SUMIF function and how to use it in Google Sheets. The SUMIF and SUMIFS functions are valuable assets for anyone working on Google Sheets, and the same rules are often transferable to Microsoft Excel.

While the number of functions and formulas at your disposal may seem intimidating, they’re there to make your data processing experience more straightforward and efficient.

Once you learn the basics of how to use Google Sheets, you will be able to work with a vast amount of data and condense it down to more understandable values within seconds.

]]>There are three methods to split first and last names we will go through today. The first and easiest method is using the split function so we will start there.

# | Method |
---|---|

1 | Split Function |

2 | Split Text To Columns |

3 | LEFT & RIGHT Functions |

There is of course one more method, you can manually split first and last names if you only have a few names to work with. However, learning how to split names using functions will enable you to quickly separate them no matter how large your data set is.

The simplest way to separate first and last names is by using the split function. This will separate first and last names into individual columns. In this example, we will use it to split each name into its own cell whenever a space is detected.

When the function runs, it will find the name "Jerry Fitzgerald" in cell A1 and look for a space where it will split the name.

When this happens, Jerry will remain in cell B1 and Fitzgerald will be inserted into C1.

Let's look at an example where that names are formatted slightly differently. If you are working with hyphenated names like Anne-Marie and would like to split them you can use "-" as the delimiter instead of a space.

We can then drag the formula to repeat the process for the rest of the names in the list.

In our third example, we will look at an example where we want to split on both spaces and hyphens. This will enable us to separate first names and double-barrelled surnames.

In this example, we use two delimiters, a space and a hyphen and function will split when it sees either of them.

When we copy the formula down we get the expected result!

The split function has two required parameters and two optional ones. However, if we do not include the optional values, Google Sheets will use their default values.

The Text argument specifies the text that needs to be split. It can be inserted directly into the function, alternatively, a cell reference can be inserted.

The Delimiter argument specifies the character (or characters) where the Text should be split. In our example, we used a space but you may use letters, numbers, or characters like commas, semi-colons, hyphens, and underscores.

Determines whether the split happens on every occurrence of the delimiter. In its default state, it is set to TRUE meaning every time the delimiter is found, a split will occur. However, if you would like to split once, this parameter can be set to FALSE.

When an empty cell is created by the split function, the remove_empty_text parameter will determine whether is removed or not. TRUE is the default state of this optional parameter and will mean empty cells will be removed. If you would like to keep the empty cells, set this value to FALSE.

You can also use the **Split text to Columns** in Google Sheets to split first and last names. This is useful if you have a list of people’s names and need to separate their first and last names into two columns. If you don’t have any other information about the people, this method is usually better than regular expressions, which can complicate matters.

You can use the tool to split your data into separate columns based on the value in the first row of your data. Here is how you do it.

**Highlight**the cells with the first and last names you would like to split.- Go to the
**Data**menu and click on**Split text to columns**. - You will then be asked to select a
**Separator**. In our case we will select space.

4. The first and last name will then be split into adjacent columns

Our final method for splitting first and last names in Google Sheets is using the LEFT and RIGHT functions. These are two separate functions that will need to be written individually.

- Copy the formula "
**=LEFT(A1,FIND(" ",A1)-1)**" and paste it onto your sheet. In this example the names to be split are in cell**A1**. If your first name is in a different cell, you may need to adjust the cell reference. - Copy the formula "
**=RIGHT(A1,LEN(A1)-FIND(" ",A1))**" and again paste it into your sheet. This function will separate the second name.

When you drag both formulas, you should have separate columns of first and second names.

The LEFT method is simple: it takes whatever name you wish to split and finds the space where you are trying to split is. The RIGHT function works in a similar way, except this time we are splitting from the other side.

The LEFT function will return the characters to the left of the space, while the RIGHT function will return the characters to the right.

These formulas combine the LEFT, RIGHT, LEN, and FIND functions. The LEFT and RIGHT functions help extract a substring of a specific text from the beginning and the end, respectively.

The LEN function will return the count of all characters used, including all delimiters - which is the reason we may need to add or subtract from the value returned.

The FIND function is helpful in locating a specific character within the string. The function returns the position of the first occurrence searched within the series. In the formula =FIND ( " ", A3 ) ), the FIND function locates the space character in the string. You may then subtract one to return the number of characters used in the first name.

When splitting first and last names in Google Sheets, you have 3 methods to choose from—SPLIT, Split text to columns, and the LEFT/RIGHT functions. The split method is the best choice when splitting a long data string into multiple columns based on different delimiters included in the string. If you are starting out we recommend the SPLIT method as it contains all the functionality required for most use name splitting use-cases.

]]>The VLOOKUP function in Excel can be used to look up specific information in a data set. The function searches for a value in the first column of a data set and returns the corresponding value from another column in the same row. The VLOOKUP function can be used to look up values in a table, a named range, or an array.

The VLOOKUP function stands for ‘Vertical Lookup,’ and is typed out like this: =VLOOKUP.

And here is the syntax of the VLOOKUP formula and its components:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

We’ll break each part of this function below so you know what information the VLOOKUP formula is seeking, and what info you’ll be inputting into each part.

The primary purpose of the VLOOKUP function is to **help you retrieve information**, especially if you’re always searching for the same type of data in a really large spreadsheet. So the VLOOKUP function can help you find data much quicker than scrolling through. For example, you can use the phone number of a client using their last name, or the price of an object in your inventory using its item code.

When you use the VLOOKUP formula in Excel, it will search for a certain value in **a vertical column (or table array) **of a table or range in your spreadsheet. It will then return a value from a different column number in the same row. You are providing it with a value, where you want to look for it, and the column range that contains the value to return.

Another way to say it is this function acts somewhat like a phonebook, where you are using a column in the table with the information you know (the person’s name) to find the information you don’t know (their phone number).

Let’s define what each section of the VLOOKUP formula actually means. Again, here is what the full syntax looks like:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

**Lookup_value:**This is the lookup value, or**the value you are searching for.**It’s the value Excel will find a match for in the left most column of your table range. It is directly followed by a comma.**Table_array:**This provides Excel with**where****you want to search.**It’s the columns in the lookup table range. The lookup table range is directly followed by a comma.**Col_index_num:**This part is**the column that****contains the result.**Excel will search this column to find the match to your lookup value. It is directly followed by a comma.**[range_lookup]:**This optional part indicates to Excel whether you want an exact match to the lookup value, or an approximate match. TRUE indicates an approximate match, and FALSE indicates an exact match. Note: If you choose not to enter anything here, it will default to TRUE.

*Note: VLOOKUP will only work properly if your data is neatly arranged as a vertical table with rows. The value you are looking for must be in the left most column of the cell range, to the left of the return value you’re looking for.*

Ready to enter all of the lookup values into the VLOOKUP function? Let’s get started.

Step 1: VLOOKUP

**In the Formula Bar **at the top of the spreadsheet, type in** =VLOOKUP(). **The other parts will go in between the parentheses.

Step 2: Lookup Value

Starting inside the parentheses, enter your lookup value. A quick way to do this is by double clicking on the VLOOKUP command and selecting the cell the search value will be entered. You can also manually input the actual lookup value or just the empty cell that will hold a value.

Place a **comma** directly after the lookup value. Here is an example of what it might look like: **=VLOOKUP(F10,**

Step 3: Table Array

Next, input the **table array, **or the range** **where Excel needs to search for the match to your lookup value. Separate each exact value with a **colon**, and end the array with a **comma**. It will look something like this: **=VLOOKUP(F10,C2:E30,**

Step 4: Column Index Number

Next, you will enter the** column index number**. Here is where you will input the numeric value of the column you think the information you need will be found, such as column number ‘2’ or ‘4’.

This column must lie to the right of your lookup values. Make sure you place a **comma **directly after it. It will be typed like this:** =VLOOKUP(F10,C2:E30,4,**

Step 5: Range Lookup

Finally, you will input the **range lookup value**, which will either be the word **TRUE or FALSE. **TRUE will find approximate matches for your lookup value, and enter FALSE to search for exact matches.

This step is optional, but keep in mind if you leave it blank, the VLOOKUP Excel formula will process with a TRUE as default. It will look something like this: **=VLOOKUP(F10,C2:E30,4,FALSE)**

Here are a couple issues you may run into with the Excel VLOOKUP function.

- Is the function just not producing any results, or coming back with an error message? Make sure you check your computer’s Language Settings. Some settings will have you place a comma [ , ] after each argument, and some will need a semicolon [ ; ]. It’s more standard to see commas, though.

- Do you keep ending up with an
*#N/A*error? This may be happening because of the FALSE that you may have inputted into the formula. If an exact match for the value you’re looking for doesn’t exist, or you’ve set your range incorrectly and it simply can’t find the correct answer, it will produce this*#N/A error.*

Note:

- Do you need to perform a Horizontal Lookup, rather than a Vertical Lookup? You can use the
__HLOOKUP function__in Excel. This will look through the horizontal rows in your table. - Want to learn a convenient hack? If you want to take your value search further than just strictly VLOOKUP or HLOOKUP, there’s a newer Microsoft Excel function that allows you to do this:
**XLOOKUP.**This function will have Excel look through any direction of columns and rows, and return exact matches by default instead of approximate matches. Only stipulation is you’ll need a Microsoft 365 subscription, and the latest version of Office.

By default, VLOOKUP uses an approximate match in Excel. What does this mean? It means that if the value you're looking for isn't an exact match, it will still return a value - AKA approximate match.

For example, if you're trying to look up the number 5 and all your values are in increments of 10 like 10, 20, 30, 40, VLOOKUP will return the value 10.

You can change this by adding a fourth argument to your function. The 4th argument is either TRUE or FALSE. If you set it to FALSE, VLOOKUP will only return an exact match, not an approximate match.

If you want to find an exact match using VLOOKUP, you need to set the 4th argument to FALSE.

You can also use MATCH to find an exact match and then use that result with INDEX to get the value you want.

If you want to use VLOOKUP with multiple criteria, there are a couple ways you can do this.

The first way is by using the IF formula. This function will check if your first criterion is met, and then return the corresponding value. You can then use another IF formula to check for your second argument. For example:

=IF(VLOOKUP(A2,B:C,2,FALSE)=D2,VLOOKUP(A2,B:C,2,FALSE),'not found')

The second way is by using the INDEX and MATCH function. This function will first check if both criteria are met and then return value.

=INDEX(B:C,MATCH(1,(A2=B:B)*(D2=C:C),0))

Both of these methods will require that your data is sorted. Sorting your data will make it easier for Excel to find the values you're looking for.

If you want to find the closest match, you can use the VLOOKUP function with the MATCH function.

The MATCH function will return the position of a value in an array or range. You can then use this position to return the corresponding value from your array or range.

=VLOOKUP(A2,B:C,MATCH(A2,B:B,-1)+1,FALSE)

The Excel MATCH function has a third argument that allows you to specify whether you want an exact match or an approximate match.

If you want an exact match, you would use a 0 as the third argument. If you want an approximate match, you would use a 1 as the 3rd argument.

However, if you want to find the closest value, you would use a -1 as the third argument.

This will tell Excel to find the largest value that is less than or equal to the value.

If you want to return multiple values, you can use the VLOOKUP function with the INDEX and MATCH function.

INDEX will return a value from a given array or range. You can use this to return multiple values from your array or range.

=VLOOKUP(A2,B:C,MATCH(A2,B:B,-1)+1,FALSE)

If you want to use VLOOKUP for partial matches, you can use the wildcard character (*). The wildcard character can be used to replace any characters in your lookup data.

You can also use the question mark (?) wildcard character to replace a single character.

If you want to find duplicate values, you can use the COUNTIF function.

COUNTIF will return the number of cells that contain a given value. You can use this to check if a value exists more than once in your table.

If you insert a column next to the lookup column, the VLOOKUP function will continue to look in the original column for the value.

To fix this, you can use the INDIRECT function. The INDIRECT function returns a cell reference or range of cells.

You can use this function to dynamically reference cells. This means that if you insert a column number, the reference will change automatically.

=VLOOKUP(A2,INDIRECT("B:B"),2,FALSE)

The INDIRECT function takes a string as an argument. The string is the reference to the cell or range of cells.

You can use the Excel INDIRECT function with the VLOOKUP function to dynamically reference a cell or range of cells. This means that if you insert a column in the table, the reference will automatically update. Make sure you don't include the FALSE part of the syntax, with the INDIRECT reference in double quotes.

VLOOKUP only returns the first match it finds. If there are multiple matches, it will only return the first one.

To fix this, you can use the INDEX and MATCH formulas. INDEX returns a value from a given array or range. You can use this to return multiple values from your array or range.

=INDEX(B:C,MATCH(1,(A2=B:B)*(D2=C:C),0))

The Excel MATCH formula returns the position of a value in an array or range. You can then use this position to return value from your array or named range.

There you have it! We hope this guide has covered everything you need to know about the VLOOKUP function in Microsoft Excel. VLOOKUP works perfectly in Excel, so long as you're up to speed. As always, if you have any questions, feel free to reach out via our Contact Us page. We’re happy to help!

]]>