57 items found

Blog Posts (45)

  • How Do You Dynamically Reference Another Sheet in Google Sheets?

    Perhaps you're working on a complex sheet that contains many sheets within one worksheet. You might be wondering how to dynamically reference another sheet in Google Sheets so that you can pull all of the data into one screen. The good news is, there is a great solution for this! So, how do you dynamically reference another sheet in Google Sheets? The best option is to use the INDIRECT formula. This is great if you have multiple sheets that are identical but have different data. You can pull all of this information dynamically into one sheet using the INDIRECT function. Continue reading to learn how to use this formula and examples of why you might want to use a formula like this! Another great way to pull data from another sheet is by using the INDEX MATCH formula. Learn how to use INDEX MATCH here! How to Use the INDIRECT Formula Similar to VLOOKUP, the INDIRECT formula allows you to find data from one sheet and dynamically displaying in another. However, there is one very important catch... In order for INDIRECT to work, your spreadsheets must be set up the exact same way. Therefore, this formula applies best to spreadsheets where you are collection the same data, only in different sheets. This might be a sales spreadsheet where you have a different sheet for every month of the year and you're recording the same data every month. In the case, you could use INDIRECT to pull this monthly data into one spreadsheet for your yearly summary. For the purposes of this article, let's use a bakery & ice cream shop as an example. Let's say that we are keeping a revenue and profit spreadsheet for our business. We have a tab/sheet for each month of the year, and we want to record the year-to-date revenue and profit on a single sheet. We have each monthly tab set up the exact same way, like this: At the top of the sheet, in yellow and green, I am using a simple SUM of columns C and D so that we can get the totals of each column. We will use the INDIRECT formula to dynamically reference the monthly sheets and bring these two totals in for each month. First, let's break down the formula: =INDIRECT(reference&"string") The reference portion of the formula is pointing to the reference point on the current sheet that matches the sheet names. In the example of our our spreadsheet, we will list out the months which matches the sheet names. The "string" portion of the formula indicates the cell that all sheets have in common where the desired data can be found. The ampersand (&) is functioning to concatenate the reference and string. In other words, the indirect formula is working the same as pointing directly to the other sheet. For example, =INDIRECT(A2&"!G1") will give you the same result as =January!G1 That being said, you might ask, why use INDIRECT when you can simply reference the cell? The answer is simple, instead of having to go to every single sheet and referencing the cell, you can write one INDIRECT formula and copy it down so it will dynamically apply to every sheet. Let's see the real-life example. Once you have the first formula in, you can simply grab the square on the lower right corner of the cell and drag it down the column for the data to be populated. Then repeat for the profit column. How to SUM the INDIRECT Formula You might have noticed in the formula above that the rows which do not have sheet names yet are returning an REF! error which means the sum formula (in bright green) does not work. No worries, we can fix this too! It's very easy. Simply append your current INDIRECT formula with an IFERROR formula, which looks like this: =IFERROR("no error","error") What this is saying is, if there is no error, return the first value and if there is an error, return the second value. In our case, if there is an error, we want it to simply return 0 as a number so that our SUM formula will work correctly. Your new formula should look like this. You can again copy the formula down the column and your sum will work correctly. =IFERROR(INDIRECT(A2&"!G1"),0) You can also use this INDIRECT formula on Microsoft Excel. In this case, excel might be showing you little green errors in your cell (even though there is no actual error). Here are instructions for how to remove these errors! Why Would I use INDIRECT When I Could Just Do a VLOOKUP? I love using VLOOKUP as it saves so much time and I can quickly pull in data from other sheets. However, it does have its limitations. For example, you have to have the same reference point on every single sheet. If we were to use VLOOKUP on our example spreadsheet, we would not be able to simply bring in the revenue without doing a lot of alterations to the sheet. VLOOKUP has its place and in some scenarios is a better option than INDIRECT, but when you are simply trying to pull in a simple value, this is a much better option!

  • How to Change URL Without Losing SEO

    Whether you're updating content on your existing site or moving your entire site to another domain name, you might be wondering how to change URL without losing SEO. When you spend a long time optimizing your site and gaining trust and authority to Google, you definitely don't want to lose your current search engine rankings! So, how do you change your URLs without losing SEO? The most effective way to do this is with 301 redirects. This will allow all of the traffic and SEO to be redirected from the old URL to the new URL. Some website platforms already have this ability to set up 301 redirects built into the site (such as Wix) and others require you to install an additional app or plugin (such as WordPress). Lets talk about why it's important to use 301 redirects and how to set them up on some of the most popular website platforms. Table of Contents Why are 301 redirects important for SEO? How to Setup a 301 Redirect on Wix How to Setup a 301 Redirect on WordPress How to Setup a 301 Redirect on Squarespace & Shopify Why are 301 redirects important for SEO? When you change the URL of a page that is already published on the internet, the old URL will display a 404 error. If this is not changed, Google will display an Index Coverage Error like this: Submitted URL not found (404): You submitted a non-existent URL for indexing. How does this affect your SEO? Well, obviously, if that old URL is ranking on Google, the page will display a 404 error and not the content that you want Google to crawl. Eventually, when Google crawls the site again, they will go to that link and see that there is no relevant content on the page and the URL will be deindexed. Your search rankings and hard work will be lost and you'll have to start over. This also applies to any backlinks that you might have from other websites. There is no way that you will be able to contact all of the site owners who link to your site and ask them to update the URL. However, with a 301 redirect, it doesn't matter. They can keep the old URL but when someone clicks on it, they will automatically be redirected to the new URL with the correct page content. Another time this will become handy is if you change your domain name. When changing the domain name on your site, all of the slugs (or portion of the url that comes after the .com) will remain the same but you'll need to update all links to redirect to the new domain name. How to Setup a 301 Redirect on Wix Wix makes it so easy to set up a redirect. There is no need to install a separate app or plugin, everything is already built in. All you need is a domain name and a premium plan (you cannot set up redirects with a free Wix URL). When I moved my website (this one) from WordPress to Wix, I set up 301 redirects for all of my blog posts. I changed the blog URL structure and didn't want to lose my currently rankings. I found that not only did my rankings remain the same, I actually increased in rankings on some of the poorer performing blog posts. This is a win! Wix makes setting up 301 redirects super simple: Go to SEO Tools in your site's dashboard. Click URL Redirect Manager. Click + New Redirect. Select Single Redirect. Note: If you want to update multiple pages which share the same path, you can select group redirect. Learn More Enter the old URL path you want to redirect from, (e.g., /about-us) under Old URL. Start typing the URL path of your new page and select it from the drop-down. Click Save & Close or click Save & Add Another to add more. Get the full support article here. How to Setup a 301 Redirect on WordPress Setting up a redirect on WordPress takes a little more effort since you need to find a reliable plugin and set it up first. A premium option is to upgrade your current SEO tool, which will often include a redirect manager as part of their premium plan. Two of my favorite SEO Plugins that include this with premium are Yoast SEO and All-in-One SEO. I have successfully ranks WordPress website with both plugins and find them to be easy to use. I do not pay for the premium plan for either, but I like that I could reduce the number of plugins I use by upgrading. Right now I have to install another plugin to do the redirects. If you don't want to pay for a premium plugin, like me, I recommend using Redirection by John Godley. I have successfully used this free plugin on many sites without any problems. The plugin has 4.5 stars with 571 ratings. One of the things that I really like about this plugin is that it allows you to see how many hits the URL has gotten with the redirect. How to Setup a 301 Redirect on Squarespace & Shopify Full disclosure: I have never setup redirects on Squarespace or Shopify, but if you're looking for instructions on how to do it, this might help. Squarespace - full instructions here In the Home menu, click Settings, then click Advanced. Click URL mappings. Click into the text field and add your redirects. Ensure the formatting is correct. Click Save. Shopify - full instructions here From your Shopify admin, go to Online Store > Navigation. Click URL Redirects. Click Create URL redirect. In Redirect from, enter the old URL that you want to redirect visitors from. You can't redirect URLs that begin with the following prefixes: /apps, /application, /cart, /carts, /orders, /shop, or /services. In Redirect to, enter the new URL that you want to redirect visitors to. If you want to redirect to your store's home page, then enter /. Click Save redirect. Keep Your SEO in Tact with 301 Redirects! I hope that know you understand how and why you should always set up 301 redirects when you're changing any of your website URLs. Don't lose all of your hard work and effort, let along the website traffic and visitors, just by missing a simple step like this. If you have any questions or comments about this topic, please leave a comment below!

  • What is the Difference Between Organic and Direct Traffic in Google Analytics?

    When analyzing your website traffic, you might be wondering what the difference is between organic and direct traffic in Google Analytics. This is important for your business or website analytics when trying to determine exactly where your traffic is coming from. So, what is the difference between organic and direct traffic in Google Analytics? In short, organic traffic is website visits that come from search engines whereas direct traffic is website visits that came from an unknown source that Google Analytics could not identify. Continue reading for a more in-depth description of both organic and direct traffic. How do I see the Organic vs Direct report in Google Analytics? Go to Google Analytics Click on Acquisition on the left menu In the submenu, click on All Traffic Next, click on Channels From here you can see the data and click on each channel to see more in-depth information on each channel: Organic Search, Direct, Referral, Social, and Other What is Organic Traffic In Google Analytics? Here is how Google themselves defines organic traffic. Traffic that finds your site through any of the default search engines appears as organic search traffic in your reports. As previously stated, and just as the name sounds, organic traffic is simply any website traffic that originates from a search engine. At the time of writing this article, Google automatically sees 70 websites as a search engine and will classify traffic from these sites as organic. Additionally, Google allows you to edit the list of search engines that will be classified as organic search! Learn how here. Checking your organic search traffic is important for measuring the effectiveness of your search engine optimization efforts. What is Direct Traffic In Google Analytics? Google defines direct traffic like this: A session is processed as direct traffic when no information about the referral source is available, or when the referring source or search term has been configured to be ignored. What does this mean? In simple terms, direct traffic is any website visit where Google Analytics can not identify where the visit came from. This could happen for a couple of different reasons. Accessing the site directly Anytime someone comes to your website by clicking on a bookmark in their browser or typing your website address directly into the URL bar, the visit will be classified as a direct visit. The visitor did not get to your site by clicking a link on another site or from a search engine. Ad Blocker Usage Ad blocking or ad filtering is a software capability for blocking or altering online advertising in a web browser, an application or a network. This may be done using browser extensions or other methods. (Wikipedia) Internet users who have an adblocking software or extension on their browser are not only blocking ads but they are also oftentimes blocking Google Analytics. This means that the Google Analytics tracking cookie on your site cannot obtain any information from the visitor because this information is "blocked". Therefore the site visit will be marked as a direct visitor because Google cannot tell where the visitor came from. They are treated as no different than a visitor who type the URL or clicked to your site from a bookmark. According to eMarketer, 1 in every 4 internet users use some sort of ad blocker on there browser. Therefore, you should not be alarmed if you see a lot of direct visits on your Google Analytics. Understanding Your Traffic Sources is Pivotal When trying to analyze the effectiveness of your marketing efforts, understanding exactly where your website traffic is coming from is very important. I hope that this article has helped you understand the difference between organic and direct traffic. If you have any questions or comments about this topic, please leave a comment below!

View All

Pages (11)

  • | Local Web Strategies

    Is the article you're reading helpful? Well, there's more where that came from! ​ Sign up below and I'll send you and update everytime a new article has been posted. ​ No spam. Just awesome tips! Sign Up Thanks for submitting!

  • Digital Marketing & Tech for Business Tips | Local Web Strategies

    Step up your Google Sheets Game Check out these cool things you can do with Google Sheets Send my free download! Check your email. Your free download is on the way! GROW MANAGE ANALYZE TOOLS Recent Articles G Suite How Do You Dynamically Reference Another Sheet in Google Sheets? Grow How to Change URL Without Losing SEO Analyze What is the Difference Between Organic and Direct Traffic in Google Analytics? Microsoft Office How to Use INDEX MATCH Across Multiple Sheets in Excel or Google Sheets Search Engine Optimization Why is Mobile SEO So Important? G Suite How to Make a Line Graph in Google Sheets with Multiple Lines Google My Business Can Two Businesses Have the Same Address on Google My Business? G Suite How to Add a Copy to Clipboard Button in Google Sheets G Suite How Do I Create a Button to Clear Data in Google Sheets?

  • | Local Web Strategies

    Business Tips Directly to Your Inbox Sign me up! I’d like to receive news and updates. Submit Thanks for submitting!

View All