#how to count without duplicates in excel pivot table | Explore Tumblr posts and blogs

dataanlystcoursetip · 2 years ago

Text

Essential Excel Skills Every Data Analyst Should Master

Excel is a cornerstone tool in the world of data analysis. Its versatility, combined with powerful features, makes it invaluable for professionals working with data every day. If you want to thrive as a data analyst, developing strong Excel skills is not just beneficial — it’s essential.

Whether you’re just starting or looking to level up your expertise, focusing on the right Excel capabilities can make your work faster, more accurate, and insightful.

In this article, we’ll explore the key Excel skills data analysts classes should master, along with practical tips on how to apply them effectively.

Data Import and Cleaning: Preparing Your Data for Analysis

Before you can analyze data, you need to get it into Excel and ensure it’s clean and reliable. Data import involves bringing information from various sources — CSV files, databases, web pages, or other software — into your workbook.

Once imported, cleaning the data is crucial. This means handling missing values by deciding whether to fill, ignore, or remove them. Removing duplicates keeps your dataset accurate, while correcting errors like inconsistent formatting or typos prevents misleading results.

Using Excel’s built-in features such as Text to Columns, Find & Replace, and Data Validation can speed up this process and improve data quality.

Data Transformation: Shaping Your Data to Tell a Story

Raw data often isn’t ready for analysis. You’ll need to transform it by manipulating and reshaping to answer your questions better. Excel functions like CONCATENATE help you combine text fields, while LEFT and RIGHT extract specific parts of strings, such as area codes from phone numbers.

VLOOKUP is a classic function for merging data from different tables, but learning INDEX-MATCH can give you more flexibility and reliability.

PivotTables allow you to pivot your data, meaning you can rearrange rows and columns quickly to summarize and explore your data from multiple angles.

Data Analysis Functions: Extracting Meaning from Numbers

A solid understanding of Excel’s data analysis functions is a must. Functions like SUMIFS and COUNTIFS let you sum or count data conditionally — for example, calculating total sales for a particular region or counting customers by segment.

AVERAGEIFS works similarly, enabling you to find average values based on multiple criteria. MAX and MIN help identify extremes, while SUMPRODUCT allows for weighted calculations that can combine multiple arrays logically.

Logical functions such as IF, AND, and OR help create dynamic formulas that change results based on conditions, essential for scenario analysis and decision-making.

Data Visualization: Making Your Insights Visible

Numbers tell a story, but visualizing those numbers makes the story easier to understand. Creating charts like bar charts, line charts, or scatter plots helps reveal trends, comparisons, and outliers.

Excel’s chart customization options allow you to adjust colors, add labels, titles, and legends, and format axes for clarity and appeal. Well-designed charts make reports more engaging and accessible to stakeholders.

Statistical Analysis: Unlocking Deeper Insights

For more advanced data work, Excel offers statistical functions to calculate measures like mean, median, and standard deviation quickly. These descriptive statistics give you a snapshot of your data’s distribution and variability.

Excel’s Data Analysis ToolPak adds capabilities for hypothesis testing, regression, and ANOVA, allowing you to perform basic statistical analysis without needing separate software.

Advanced Formulas: Taking Your Analysis Further

Once you’re comfortable with basics, exploring advanced formulas can dramatically enhance your efficiency. Array formulas, for example, perform calculations on multiple values at once, reducing the need for repetitive tasks.

The combination of INDEX and MATCH is a powerful alternative to VLOOKUP, especially for large datasets where performance and flexibility matter.

Data Validation and Error Handling: Keeping Your Data Reliable

To ensure the integrity of your data, learn to implement data validation rules. These rules restrict inputs to expected formats or ranges, preventing incorrect or inconsistent data from entering your sheets.

Equally important is setting up error handling within formulas using IFERROR or ISERROR functions, which help manage and display meaningful messages instead of cryptic error codes. This practice makes spreadsheets more user-friendly and trustworthy.

Time Series Analysis: Working with Dates and Trends

If your data involves dates, mastering Excel’s date and time functions will help you analyze trends over time effectively. Calculating moving averages smooths out fluctuations, highlighting underlying trends.

You can also explore seasonality by comparing data across different time periods, helping businesses plan better and forecast accurately.

Quick Recap: Key Excel Skills for Data Analysts

Import and clean data from diverse sources to ensure quality

Transform and manipulate data using functions like CONCATENATE and VLOOKUP

Use conditional functions such as SUMIFS and IF to analyze data effectively

Visualize data with charts for clear, impactful storytelling

Apply statistical tools for deeper data understanding

Build advanced formulas including array functions and INDEX-MATCH

Maintain data integrity with validation and error handling

Analyze time-based data for trend and seasonality insights

Conclusion

Excel remains a fundamental skill for any data analyst aiming to deliver actionable insights. From importing raw data to creating advanced visualizations and statistical analysis, mastering these core Excel skills will boost your confidence and effectiveness.

Start by practicing with your own datasets, experiment with the features highlighted here, and seek out tutorials or courses to deepen your knowledge. With steady practice, Excel will become your trusted partner in turning data into decisions.

If you want recommendations for beginner-friendly Excel courses or project ideas to get hands-on experience, feel free to ask.

#What Excel skills do data analysts need?#Data Analyst

0 notes

blogexceltutorial · 2 years ago

Text

How to Count Without Duplicates in Excel

We make you learn, how to count without duplicates in excel? How do I count excluding duplicates in Excel? How to count unique values excluding duplicates in excel? How to count unique and distinct values in excel?

#excel count duplicate values only once #how to count without duplicates in excel pivot table #count unique values excel #excel count unique text #count unique names in excel

1 note · View note

basicexceltutorial3-blog · 3 years ago

Text

Count If Not Duplicate in Excel

Countif without duplicates

You can count the number of duplicates using the COUNTIF function in Excel. To do this, select a cell in the table, and type the COUNTIF function into cell B2. Then, change the cells B2 and E2 to reflect the count in column E. This will give you a list of all the duplicate grades. Then, you can sort the result by a different criteria. To find the duplicate count, use the COUNTIF function on each cell.

The CountIF function will count the number of times each value appears in a data range, and will return an array of numbers. CountIF formulas modify the numbers in the array to make them one or multiples of one. Once you've sorted the data, you can work on it by copying and pasting the duplicates to different sheets. You can also use the CountIf formula on a pivot table.

The COUNTIF function counts cells with quantity five. In this example, it would count the cell containing the word 'Lemonade', and count the cell that has a leading space. However, you can use this formula with multiple criteria, but this will require some advanced knowledge of Excel programming. You can also split the COUNTIF formula into several parts, and test each one to make sure it works. To learn how to use COUNTIF for multiple criteria, continue reading!

COUNTIF is an Excel statistical function that allows you to count the number of times a value occurs in a specific column. You can use it to compare numbers and see which values are identical. If one of these duplicate values appears three times in a row, you can use the COUNTIF function to remove it. The COUNTIF function can be expanded to include more criteria, such as dates, if they are consecutive. In addition, COUNTIF will find values in a column that are identical in all columns.

COUNTIF is another useful Excel function for searching for cells with similar values. This function counts cells that have a date greater than or equal to the current date. When you use COUNTIF, you can count cells that contain a date greater than or equal to the value. The COUNTIF function can also match cells that contain the date greater than or less than one. For example, if a cell contains a date greater than 1 Jun 2014, the COUNTIF function will count the cells that contain that date.

COUNTIF is a useful Excel function for removing duplicate values. This formula counts the number of times a particular value appears in a cell range. You can use it with a text column, or with a range of values. The COUNTIF function will give you the number of duplicate values in a column if it finds any. If a value is duplicated in a column, it will be removed, but the result will still be a value of three if it's within the same cell.

When using COUNTIF, you can set conditions to search for duplicates in a specific range. For example, if you'd like to find all the juices that have the same name, you can use a wildcard character (*) to find them. Another useful option is to use COUNTIF with an OR condition to look for duplicates. This option is useful for identifying multiple values in a single cell.

1 note · View note

hostingloading4 · 4 years ago

Text

Merge Excel Files Tool

Merge Excel File Tool Download

Merge Excel Files Tool_v1

But for many Excel users, this task can be quite challenging and very time-consuming. The good news is, that there are several excel comparison tools available which make it very easy to merge excel files. Here are my favorite tools for merging Excel Files: Synkronizer. How to Merge Excel Files, Spreadsheets and Workbooks with Excel Merger: A Step by Step Guide Step 1: Add files to Excel Merger There are two ways of doing that. First you can drag and drop your files to the. Step 2: Set options Since you are merging files, select “Files” in the Merge dropdown. Select the excel file you want to the merge other files into. Finally, to merge Excel files, check the Create a copy checkbox, select (move to end) and click OK. Selecting (move to end), moves the excel worksheet you are merging to the end of the worksheet you are merging it into. RDBMerge, Excel Merge Add-in for Excel for Windows. RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook. Install the RDBMerge utility. 1) Download the correct version and extract it to a local directory. 2) Copy RDBMerge.xla(m) to a unprotected directory on your system.

Choose Tools > Combine Files. The Combine Files interface is displayed with the toolbar at the top.

Drag files or emails directly into the Combine Files interface. Alternatively, choose an option from the Add Files menu. You can add a folder of files, a web page, any currently open files, items in the clipboard, pages from a scanner, an email, or a file you combined previously (Reuse Files).

Note:

If you add a folder that contains files other than PDFs, the non-PDF files are not added.

In the Thumbnail view, drag-and-drop the file or pageinto position. As you drag, a blue bar moves between pages or documentsto indicate the current position.

In the Thumbnail view, hover over the page or file and then click the Expand pages thumbnail . In expanded view, you can easily move the individual pages among the other pages and documents.

To collapse the pages, hover over the first page and then click the Collapse Document thumbnail .

In the Thumbnail view, hover over the page, and then click the Zoom thumbnail .

In the Thumbnail view, hover over the page and then click the Delete thumbnail .

In the List view, click the column name that you wantto sort by. Sigil for marriage prayer. Click again to sort in reverse order. The order of filesin the list reflects the order of the files in the combined PDF.Sorting rearranges the pages of the combined PDF.

In the List view, select the file or files you want to move. Then click the Move Up or Move Down button.

Click Options, and select one of the file size options for the converted file:

Reduces large images to screen resolution and compresses the images by using low-quality JPEG. This option is suitable for onscreen display, email, and the Internet.

Note: If any of the source files are already PDFs, the Smaller File Size option applies the Reduce File Size feature to those files. The Reduce File Size feature is not applied if either the Default File Size or Larger File Size option is selected.

Create PDFs suitable for reliable viewing and printing of business documents. The PDF files in the list retain their original file size and quality.

Creates PDFs suitable for printing on desktop printers. Applies the High Quality Print conversion preset and the PDF files in the list retain the original file size and quality.

Note:

This option may result in a larger file size for the final PDF.

In the Options dialog box, specify the conversion settings as needed, then click OK.

When you have finished arranging the pages, click Combine.

A status dialog box shows the progress of the file conversions.Some source applications start and close automatically.

Home | Products |Purchase |

Merge Excel File Tool Download

FAQ |

Merge Excel Files Tool software can merge multiple excel sheets into one new sheet or merge excel workbooks into one new workbook with multiple worksheets. The software also can import one or more CSV files, XML files, TXT files into a blank MS Excel file, and insert them all into one sheet or individual sheets.

You may have to merge excel files into one new sheet or merge excel workbooks into one new workbook, then Merge Excel Files Tool software is your right choice in simplifying your tedious merging work. read more

Deep partial thickness burn.

Download Free Merge Excel Files Tool Please download our software and use it .

The Merge Excel Files Tool is including the technical support, unlimited software upgrades during all software life time.

Merge Excel Files Tool_v1

The software add advanced data analysis capabilities to Microsoft Excel and are guaranteed to save you time and speed up your work.With the tools you can merge, split, match, filter, remove duplicates, query, create Crosstab / Pivot Table, summarize, count , average, maximum, minimum, median, subtotal, count blank and SQL Query your data. Over 50 powerful features are included in this easy-to-use package. read more

The software can split a sheet into multiple sub sheets by the field in columns. You may have to split a very large worksheet into sub sheets by the field in columns, then the software is your right choice in simplifying your tedious splitting work. It worked smoothly and quickly, even with large worksheets, thereby saves your time. read more

The software can save each excel sheet as separate excel file. The program supports command line interface, So, you can run it with necessary parameters in a batch mode from the command line or from Windows scheduler without human beings. read more

The Software is a batch csv converter that converts Excel to CSV files. It allows you to Save one or more Excel to CSV (comma-separated values) files, also can save each sheet as an individual file.The program supports command line interface. read more

The software allows you to create SQL queries by clicking and arranging visual elements instead of writing SQL code even if you don't understand SQL. It can query Access, Excel Using SQL and execute SQL insert sheet for excel data in seconds. read more

Zoom manycam app. Connect ManyCam to Zoom. Getting Started Introduction to ManyCam. Getting started with ManyCam. ManyCam tray icon. ManyCam Main Live window. ManyCam Interface. ManyCam Video Settings - Quality & Performance. ManyCam is the go-to software to enhance your live video on streaming platform, video conferencing app and distant classes. Add multiple cameras and video sources, such as mobile and PowerPoint, use virtual backgrounds, create layers and presets, screencast desktop, and more. Zoom is the leader in modern enterprise video communications, with an easy, reliable cloud platform for video and audio conferencing, chat, and webinars across mobile, desktop, and room systems. Zoom Rooms is the original software-based conference room solution used around the world in board, conference, huddle, and training rooms, as well as executive offices and classrooms.

The software can merge excel workbooks into one new workbook with multiple worksheets Are you still bothered by the cumbersome job of merging multiple excel workbooks into one workbook? You may have to merge multiple excel sheets into one workbook, then it is your right choice in simplifying your tedious merging Work. read more

The software can Protect or Unprotect Multiple Excel Worksheets and Workbooks. It is very simple to use. It can batch Protect or Unprotect Multiple Excel Worksheets and workbooks, thereby saves your time! read more

The software can convert Excel Dates to Weekday, Month, Quarter, Year, Day of year, Hour, Minute, Second; Convert Text to Proper Case, Lower Case, Upper Case; Convert Excel Data Types to General or Text; Convert phone numbers to a uniform format; Convert ZIP to City State ZIP; Convert City, State to ZIP. read more

0 notes

seotipsandtricks-me · 6 years ago

Photo

Here’s a list of some of the less well known Excel formulas and macros that regularly come in handy for keyword marketers. That could be SEOs, PPCs or anyone who works with large spreadsheets containing keywords and associated data like search volume, CPC & categories. Think of it as an excel cheat sheet designed for keyword marketers, but useful for anyone wanting to grow their Excel bag of tricks. Enjoy! FORMULAS Get domain from URL Get subdomain from URL Remove first x characters from cell Remove last x characters from a cell Group keyword phrases automatically based on words they contain Word count Find out if a value exists in a range of other values Get true or false if a word or string is in a cell Remove first word from cell (all before & including 1st space) Replace the first word in a cell with another word Super trim – more reliable trimming of spaces from cells Perform text-to-columns using a formula Extract the final folder path from a URL Extract the first folder path from a URL Remove all text after the xth instance of a specific character Create an alphabetical list of column letters for use in other formulas Count instances of a character in a cell Count the number of times a specific word appears in a cell Return true if there are no numbers in a cell Get the current column letter for use in other formulas Put your keywords into numbered batches for pulling seasonal search volume data Word order flipper Find the maximum numerical value in a row range, and return the column header Find position of nth occurrence of character in a cell Get all characters after the last instance of a string Get all characters after the first instance of a string Get URL path from URL in Google Analytics format Get the next x characters after a string VBA Convert all non-clickable URLs in your spreadsheet to clickable hyperlinks Conditional formatting by row value Remove duplicates individually by column Merge adjacent cells in a range based on identical value Remove all instances of any text between and including 2 characters Highlight mis-spelled words Lock all slicers in position Split delimited values in a cell into multiple rows with key column retained Make multiple copies of a worksheet at once Add a specific number of new rows based on cell value Column stacker Superfast find and replace for huge datasets Paste all cells as values in a worksheet in the active range Format all cells to any format without having to select them Formula activation – insert equals at the beginning for a range of cells Consolidate all worksheets from multiple workbooks into one workbook Fast deletion of named columns Find and replace based on a table Unhide all sheets in a workbook Change pivot table data source for all pivot tables on a worksheet Convert all ‘numbers stored as text’ to general Get domain from URL: =LEFT(A2,FIND("/",A2,9)) This works by bringing back everything to the left of the first trailing slash found after the initial 2 in ‘http..://’, which in a URL is the slash occurring after the TLD. Get subdomain from URL: =IF(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(".",A2)),"http://",""),".",""),"https://",""),"domain","")="","none",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(".",A2)),"http://",""),".",""),"https://",""),"domain","")) When you just need the subdomains in a big list from a bunch of differently formatted URLs. This formula works regardless of the presence of the protocol. What it lacks in elegance, it more than makes up for in usefulness. Remove first X characters from cell: =RIGHT(A1,LEN(A1)-X) If there’s something consistent that you want to remove from the front of data in your cells, such as an html tag like

, you can use this to remove it by specifying its length in characters in this formula, so X would be 7 in this case. Remove last X characters from a cell: =LEFT(B2,LEN(B2)-X) You might use this to remove the trailing slash from a list of URLs, for example, with X as 1. Group keyword phrases automatically based on words they contain: =IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$200,A2),$D$2:$D$200),"/") This little chap deserves a blog post all its own. Here’s what it does: Example: Bulk categorisation of keywords by colour and hair type groups. Using the formula to group your keywords: $C$2:$C$200 is your string-to-search-for range (the list of all the possible words you want to check for in the keyword). $D$2:$D$200 is your Label to return when string found, put it in the next column along lined up (this can just be the word you’re checking for if you want – same as above) A2 is the cell containing the keyword string which you are searching to see if it contains any of the listed strings so you can label it as such “/” is what gets returned when none of the strings are matched Using the formula Word count: =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1) See how many words are in your keyword to identify if it’s long tail and get a measure of potential intent. Find out if a value exists in a range of other values: =ISNUMBER(MATCH(A2,B:B,0)) This is my favourite, so often we just need to know if URLs in list A are contained within list B. No need to count vlookup columns or iferror. It gives TRUE or FALSE. Get TRUE or FALSE if a word or string is in a cell: =ISNUMBER(SEARCH("text-to-find",A2)) If you fancy a break from using the ‘contains’ filter, this can be a way to get things done faster and in a more versatile way. Remove first word from cell (all before & including 1st space): =RIGHT(A2,LEN(A2)-FIND(" ",A2)) To remove the last word instead, just use LEFT instead of RIGHT. Replace the first word in a cell with another word: =REPLACE(A2,1,LEN(LEFT(A2,FIND(" ",A2)))-1,"X") “X” is the word you want to replace the incumbent first word with, or this can be a cell reference. Super trim – more reliable trimming of spaces from cells: =TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32))) Sometimes using =TRIM() fails because of an unconventional space character from something you’ve pasted into Excel. This gets them all. Perform text-to-columns using a formula: =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:A2)-1)*LEN($A2))+1,LEN($A2))) This is handy for template building. It provides a way of doing text-to-columns automatically with formulas, using a delimiter you specify. In the example, space ” ” is used as the delimiter. Extract the final folder path from a URL: =IF(AND(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))=3,RIGHT(A2,1)="/"),"",IF(RIGHT(A2,1)="/",RIGHT(LEFT(A2,LEN(A2)-1),LEN(LEFT(A2,LEN(A2)-1))-FIND("@",SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/","@",LEN(LEFT(A2,LEN(A2)-1))-LEN(SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/",""))),1)),RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1)))) Good for when you need to get just the last portion of a URL, that pertains to the specific page: Extract the first folder path from a URL: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))>3,LEFT(RIGHT(A2,LEN(A2)-FIND("/",A2,9)),FIND("/",RIGHT(A2,LEN(A2)-FIND("/",A2,9)))-1),RIGHT(A2,LEN(A2)-FIND("/",A2,9))) Good for extracting language folder. Remove all text after the Xth instance of a specific character: =LEFT(A2,FIND(CHAR(160),SUBSTITUTE(A2,"/",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-0))) Say you want to chop the last folder off a URL, or revert a keyword cluster to a previous hierarchy level. The “/” is the character where the split will occur, change it to whatever you want. The “-0” at the end chops off everything after the last instance. Changing it to -1 would chop off everything after the penultimate instance, and so on. Create an alphabetical list of column letters for use in other formulas A,B,C…AA,BB etc: =SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"") Unlike with numbers, Excel doesn’t automatically give you the next letter of the alphabet if you drag down after selecting cells with ‘a’ and ‘b’ but you can use this to achieve that effect. It runs through the columns, so it will keep working past Z, giving you AA and AB etc. That’s handy for making indirect references in formulas. Count instances of a character in a cell: =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) Countif “*”&”x”&”*” doesn’t cut it for this task because it counts cells, not occurrences. The example here is for ” ” space character. Count the number of times a specific word appears in a cell: =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) The formula above works for individual characters, but if you need to count whole words this will work – handy for checking keyword inclusion in landing page copy for SEO. In the example, B2 should contain the word you are counting the instances of within A2. Return TRUE if there are no numbers in a cell: =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B28))<1 Change the end to >0 to show TRUE if there are numbers present. Handy for isolating and removing cells of data which can be identified as unwanted by the presence or absence of a number, such as a mix of item names and item product codes when you only want the item names. Get the current column letter for use in other formulas: =MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2) If you’re using indirect references and want a fast way to just get the current column letter placed into your formula, use this. Put your keywords into numbered batches for pulling seasonal search volume data: =IF(A2=43,1,A2+1) To save you having to count out 2,500 keywords each time. This batches them up so you just have to filter for the batch number, ctrl A, ctrl C, ctrl V – 43 is the number of keywords in your list divided by 2500, which is the keyword planner limit. Use the blank row insertion macro to make the batches easily selectable. Word order flipper: =TRIM(MID(F18,SEARCH(" ",F18)+1,250))&" "&LEFT(F18,SEARCH(" ",F18)-1) Turns ‘dresses white wedding’ into ‘white wedding dresses’. Use it in steps inside itself to further rearrange words in a different order. Find the maximum numerical value in a row range, and return the column header: =INDEX($A$1:$F$1,MATCH(MAX(A2:F2),A2:F2,0)) So if your column headers are months or categories, this brings back which one contains the highest value for that row. Useful for showing which month has the highest search volume for a keyword. Find position of nth occurrence of character in a cell: =FIND(CHAR(1),SUBSTITUTE(A1,"c",CHAR(1),3)) Useful as a part of other formulas. Get all characters after the last instance of a string: =SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," > ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," > ","")))/LEN(" > ")))),"> ","") Gets ‘category 3’ from ‘category 1 > category 2 > category 3’, splitting on the last ‘>’. Get all characters after the first instance of a string: =TRIM(MID(A2,SEARCH(" > ",A2)+LEN(" > "),255)) Like the above, but chops off the first category e.g. gets ‘category 2 > category 3’ from ‘category 1 > category 2 > category 3’, splitting on the first ‘>’. Get URL path from URL in Google Analytics format: ="/"&RIGHT(A2,LEN(A2)-FIND("/",A2,9)) Gets ‘/folder/file’ from ‘http://www.domain.com/folder/file’, I use this to convert URLs to the path format used in Google Analytics exports when I need to vlookup data from the export into another sheet containing full URLs. You could do a find and replace instead, but that doesn’t catch the subdomains and other oddities you may have in your URL list. Get the next x characters after a string: This one is cool. If your cell contained ‘product ID:0123 london’, you could tell this formula to get ‘0123’ based on the presence of ‘product ID:’ in front of it. It says ‘find this, and bring back the next x characters’. =IFERROR(LEFT(RIGHT(A2,LEN(A2)-(SEARCH("STRING",A2)+6)),6),"") There are 3 parts you need to change. Replace STRING with your string to search for e.g. ‘product ID:’ Replace ‘+6’ with the length of your string to search for, so for ‘product ID:’ it would be ‘+11’ Replace the next number, ‘6’, with the number of characters you want to capture after the end of the string to search for. So to capture ‘0123’ from ‘product ID:0123’ you’d put ‘4’. =IFERROR(LEFT(RIGHT(A2,LEN(A2)-(SEARCH("product ID:",A2)+11)),4),"") So it’s a bit like regex capture. I used this to get the width and height of images in raw HTML. EXCEL VBA MODULES VBA does stuff to your spreadsheets by pressing a button. Usually this is stuff that would take a long time to do (or be hard / impossible to do) using the normal Excel ribbon & formula capabilities. To use these: Save your workbook as .xlsm Reopen it and hit alt + f11 In the menu, insert > module Paste in the code Press the play button There’s no need to understand the code. But be careful to save a backup copy of your workbook before running any of these – they can’t be undone with ctrl + z! Convert all non-clickable URLs in your spreadsheet to clickable hyperlinks: So you can visit the URLs easily if you need to e.g. for optimisation of a lot of pages, so you don’t have to mess about double clicking each one to get it ready. Sub HyperAdd() For Each xCell In Selection ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula Next xCell End Sub Conditional formatting by row value: So the colour intensity is relative to each row only, rather than the entire range. You need to use this to complete the search landscape document seasonality tab. Sub NewCF() Range("B1:P1").Copy For Each r In Selection.Rows r.PasteSpecial (xlPasteFormats) Next r Application.CutCopyMode = False End Sub Remove duplicates individually by column: If you have a lot of columns, each of which needs duplicates removing individually e.g. if you have a series of category taxonomies to clean – you can’t do this from the menu: Sub removeDups() Dim col As Range For Each col In Range("A:Z").Columns With col .RemoveDuplicates Columns:=1, Header:=xlYes End With Next col End Sub Merge adjacent cells in a range based on identical value: To save you doing it individually when you need to make a spreadsheet look good: Sub MergeSameCell() 'Updateby20131127 Dim Rng As Range, xCell As Range Dim xRows As Integer xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Application.DisplayAlerts = False xRows = WorkRng.Rows.Count For Each Rng In WorkRng.Columns For i = 1 To xRows - 1 For j = i + 1 To xRows If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then Exit For End If Next WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge i = j - 1 Next Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Remove all instances of any text between and including 2 characters from a cell (in this example, the < and >): Especially good for removing HTML tags from screaming frog extractions, kind of a stand in for regex. Public Function DELBRC(ByVal str As String) As String While InStr(str, "<") > 0 And InStr(str, ">") > InStr(str, "<") str = Left(str, InStr(str, "<") - 1) & Mid(str, InStr(str, ">") + 1) Wend DELBRC = Trim(str) End Function Highlight mis-spelled words: This can help you identify garbled / nonsense keywords from a large set, or just to spellcheck in Excel if you need to. Sub Highlight_Misspelled_Words() For Each cell In ActiveSheet.UsedRange If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 3 Next End Sub Lock all slicers in position: If you send Excel documents to clients with slicers in them, you might worry that they’ll end up moving the slicers around while trying to use them – a poor experience which makes your document feel less professional. But there’s a way around it – run this code and your slicers will be locked in place across all worksheets, while still operational. This effect persists when the document is re-saved as a normal .xlsx file. Option Explicit Sub DisableAllSlicersMoveAndResize() Dim oSlicerCache As SlicerCache Dim oSlicer As Slicer For Each oSlicerCache In ActiveWorkbook.SlicerCaches For Each oSlicer In oSlicerCache.Slicers oSlicer.DisableMoveResizeUI = True Next oSlicer Next oSlicerCache End Sub Split delimited values in a cell into multiple rows with key column retained: It’s easy to put a delimited string (Keyword,Volume,CPC…) into columns using text-to-columns but what if you want it split vertically instead, into rows? This can help: Sub SliceNDice() Dim objRegex As Object Dim X Dim Y Dim lngRow As Long Dim lngCnt As Long Dim tempArr() As String Dim strArr Set objRegex = CreateObject("vbscript.regexp") objRegex.Pattern = "^s+(.+?)$" 'Define the range to be analysed X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2 ReDim Y(1 To 2, 1 To 1000) For lngRow = 1 To UBound(X, 1) 'Split each string by "," tempArr = Split(X(lngRow, 2), ",") For Each strArr In tempArr lngCnt = lngCnt + 1 'Add another 1000 records to resorted array every 1000 records If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 2, 1 To lngCnt + 1000) Y(1, lngCnt) = X(lngRow, 1) Y(2, lngCnt) = objRegex.Replace(strArr, "$1") Next Next lngRow 'Dump the re-ordered range to columns C:D [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) End Sub Make multiple copies of a worksheet at once: If you are making a reporting template for example, and want to get the sheets for all 12 weeks created in one go: Sub swtbeb4lyfe43() ThisWS = "name-of-existing-worksheet" '# of new sheets s = 6 '# of new sheets For i = 2 To s Worksheets("name-of-existing-worksheet-ending-with-1").Copy After:=Worksheets(Worksheets.Count) ActiveSheet.Name = ThisWS & i Next i End Sub Add a specific number of new rows based on cell value: Saves repeatedly using insert row, pressing F4 etc: Sub test() On Error Resume Next For r = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1 For rw = 2 To Cells(r, "E").Value + 1 Cells(r + 1, "E").EntireRow.Insert Next rw, r End Sub Column stacker: This one’s great when you have lots of columns of information that you want to be combined all into one master column: Sub ConvertRangeToColumn() 'UpdatebyExtendoffice Dim Range1 As Range, Range2 As Range, Rng As Range Dim rowIndex As Integer xTitleId = "KutoolsforExcel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8) rowIndex = 0 Application.ScreenUpdating = False For Each Rng In Range1.Rows Rng.Copy Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True rowIndex = rowIndex + Rng.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub Superfast find and replace for huge datasets: To match partial cell, change to ‘X1Part”. Sub Macro1() Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' fill your range in here Range("S2:AJ252814").Select ' choose what to search for and what to replace with here Selection.Replace What:="0", Replacement:="/", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.CalculateFull End Sub Paste all cells as values in a worksheet in the active range: For when your spreadsheet is too slow to do it manually Sub ToVals() With ActiveSheet.UsedRange .Value = .Value End With End Sub Format all cells to general format or whatever you like, without having to select them: Another good one for when your spreadsheet is too slow. Sub dural() ActiveSheet.Cells.NumberFormat = "General" End Sub Formula activation – Insert equals at the beginning for a range of cells: If you’re making something complex with a lot of formulas that you don’t want switched on yet, but you want to be able to use other formulas at the same time (i.e. can’t turn off calculations) this can help. It’s also good for just adding things to the start of cells: Sub Insert_Equals() Application.ScreenUpdating = False Dim cell As Range For Each cell In Selection cell.Formula = "=" & cell.Value Next cell Application.ScreenUpdating = True End Sub Consolidate all worksheets from multiple workbooks in a folder on your computer into a single workbook with all the worksheets added into it: If you have a big collection of workbooks which you want consolidated into one, you can do it in a single step using this macro. Especially good for when the workbooks you need to consolidate are big and slow. Sub CombineFiles() Dim Path As String Dim FileName As String Dim Wkb As Workbook Dim WS As Worksheet Application.EnableEvents = False Application.ScreenUpdating = False Path = "C:scu" 'Change as needed FileName = Dir(Path & "*.xl*", vbNormal) Do Until FileName = "" Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName) For Each WS In Wkb.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Next WS Wkb.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub Fast deletion of named columns in a spreadsheet which is responding slowly: Sometimes, one does not simply ‘delete a column’. This is for those times. Sub Delete_Surplus_Columns() Dim FindString As String Dim iCol As Long, LastCol As Long, FirstCol As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With FirstCol = 1 With ActiveSheet .DisplayPageBreaks = False LastCol = .Cells(3, Columns.Count).End(xlToLeft).Column For iCol = LastCol To FirstCol Step -1 If IsError(.Cells(3, iCol).Value) Then 'Do nothing 'This avoids an error if there is a error in the cell ElseIf .Cells(3, iCol).Value = "Value B" Then .Columns(iCol).Delete ElseIf .Cells(3, iCol).Value = "Value C" Then .Columns(iCol).Delete End If Next iCol End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Find and replace based on a table in another worksheet: Use X1Part for string match & replace within a cell, or X1 whole for whole cell match & replace: Sub Substitutions() Dim rngData As Range Dim rngLookup As Range Dim Lookup As Range With Sheets("Sheet1") Set rngData = .Range("A1", .Range("A" & Rows.Count).End(xlUp)) End With With Sheets("Sheet2") Set rngLookup = .Range("A1", .Range("A" & Rows.Count).End(xlUp)) End With For Each Lookup In rngLookup If Lookup.Value <> "" Then rngData.Replace What:=Lookup.Value, _ Replacement:=Lookup.Offset(0, 1).Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False End If Next Lookup End Sub Unhide all sheets in a workbook: Do your hidden sheets tell the tale of 1,000 previous clients? You don’t need me to tell you this can look unprofessional. Bring those hidden sheets up from the dregs in one go with this vba code so you can delete them. Otherwise, you’ll have to tediously unhide them 1 by 1 – there is no option in the interface to do this all at once. Sub Unhide_All_Sheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wksEnd Sub Change pivot table data source for all pivot tables on a worksheet: Your data source has changed. You have 12 pivot tables to update. You just lost your lunch break. Or did you? To update all their data sources in one fell swoop, replace WORKSHEETNAME with the name of your worksheet and DATA with the name of your data source: Sub Change_Pivot_Source() Dim pt As PivotTable For Each pt In ActiveWorkbook.Worksheets("WORKSHEETNAME").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="DATA") Next ptEnd Sub Convert all ‘numbers stored as text’ to general: “Number stored as text!” .We’ve all seen it. We’ve all been annoyed by it. I had several thousand rows to convert and this took minutes, not seconds. Skip it all using this, replacing your range: Sub macro()Range("AG:AK").Select 'specify the range which suits your purposeWith SelectionSelection.NumberFormat = "general".Value = .ValueEnd WithEnd Sub There are other ways to do it, but if you have a big dataset, this is the fastest way. BONUS TIPS If you have a slow spreadsheet that’s locked up Excel while it’s calculating, but you still need to use Excel for other stuff, you can open a completely new instance of Excel by holding Alt, clicking Excel in the taskbar, and answering ‘yes’ to the pop up box. This isn’t just a new worksheet – it’s a totally new instance of Excel. To open a new workbook in the same instance of Excel a bit more quickly than usual when you already have workbooks open, you can use a single middle mouse click on Excel in the taskbar. We’ve written some other blog posts about excel, you can find them here: 5 GREAT USES OF THE IF FORMULA IN EXCEL (YOU MAY NOT KNOW ABOUT) 10 GREAT EXCEL SHORTCUTS (YOU MIGHT NOT KNOW ABOUT) 5 GREAT TIME-SAVING EXCEL TIPS (YOU MAY NOT KNOW ABOUT) The post Excel Cheat Sheet for Keyword Marketers appeared first on FOUND.

#8392

0 notes

rafi1228 · 6 years ago

Link

Microsoft Excel for Business – Excel Formulas and Functions, Excel Charts, Excel Analytics, Shortcuts & Excel Macros

Microsoft Excel

Created by Start-Tech Academy

Last updated 8/2019

English

What you’ll learn

Solid understanding of Microsoft Excel

Knowledge of all the essential Excel formulas

Become proficient in Excel data tools like Sorting, Filtering, Data validations and Data importing

Master Excel’s most popular lookup functions such as Vlookup, Hlookup, Index and Match

Harness full potential of Excel by creating Pivot tables with slicers

Make great presentations using the Conditional and Table formatting options

Visually enchant viewers using Bar charts, Scatter Plots, Histograms, Waterfall charts etc.

Increase your efficiency by learning how to create and use important Excel shortcuts

Become competent in using advanced Analytics techniques in Excel such as Regression, Goal seek and Solver

Learn Automation techniques by using Macros

Explore fun and exciting use cases of Excel in our bonus videos

Microsoft Excel

Requirements

You will need a PC with any version of Excel installed in it

Microsoft Excel

Description

6 Reasons why you should choose this Excel course

Carefully designed curriculum teaching you only the most used functionalities of Excel in business environment

Concise – you can complete this course within one weekend

Business related examples and case studies

Ample practice exercises because Excel requires practice

Downloadable resources – Microsoft Excel

Your queries will be responded by the Instructor himself

Start using Excel to its full potential to become proficient at your Excel tasks today!

Either you’re new to Excel, or you’ve played around with it but want to get more comfortable with Excel’s advanced features. Either way, this course will be great for you.

A Verifiable Certificate of Completion is presented to all students who undertake this Excel course.

Why should you choose this course?

This is a complete and concise tutorial on MS Excel which can be completed within 4 hours. We know that your time is important and hence we have created this fast paced course without wasting time on irrelevant Excel operations.

What makes us qualified to teach you?

The course is taught by Abhishek and Pukhraj. Instructors of the course have been teaching Data Science and Machine Learning for over a decade.

We are also the creators of some of the most popular online courses – with over 150,000 enrollments and thousands of 5-star reviews like these ones:

I had an awesome moment taking this course. It broaden my knowledge more on the power use of Excel as an analytical tools. Kudos to the instructor! – Sikiru

Very insightful, learning very nifty tricks and enough detail to make it stick in your mind. – Armand

Our Promise

Teaching our students is our job and we are committed to it. If you have any questions about the course content, practice sheet or anything related to any topic, you can always post a question in the course or send us a direct message.

Download Practice files, take Quizzes, and complete Assignments

With each lecture, there is a practice sheet attached for you to follow along. You can also take quizzes to check your understanding of concepts. Each section contains a practice assignment for you to practically implement your learning. Solution to Assignment is also shared so that you can review your performance.

What is covered in this course?

This course covers everything you need to crack Excel in the professional work place.

Below are the Excel course contents of this complete and concise course on Microsoft Excel:

Introduction – In this video, the structure and contents of the course are discussed.

Mathematical Functions – This lecture covers Mathematical formulas such as SUM, AVERAGE,RAND, MIN & MAX, SUMPRODUCT.

Textual Formulas – This Excel lecture covers Textual formulas such as TRIM, CONCATENATE, SUBSTITUTE, UPPER & LOWER, LENGTH, LEFT, RIGHT & MID

Logical Formulas – This lecture covers Logical formulas such as AND & OR, IF, COUNTIF, SUMIF

Date-time (Temporal) Formulas – This lecture covers Date-time related functions such as TODAY & NOW, DAY, MONTH & YEAR, DATEDIF & DAYS

Lookup Formulas – This Excel lecture covers Lookup formulas such as VLOOKUP, HLOOKUP, INDEX, MATCH

Data Tools – This lecture covers Data operating tools such as Data Sorting and Filtering, Data validation, Removing duplicates, Importing Data (Text-to-columns)

Formatting data and tables – This Excel lecture covers data formatting options such as coloring, changing font, alignments and table formatting options such as adding borders, having highlighted table headers, banded rows etc.

Pivot Tables – This Excel lecture covers Pivot tables end-to-end.

Charts – This Excel lecture covers charts such as, Bar/ Column chart, Line Chart, Scatter Plot, Pie & Doughnut charts, Statistical Chart – Histogram, Waterfall, Sparklines

Excel Shortcuts – This lecture will introduce you to some important shortcuts and teach you how to find out the shortcut for any particular excel operation.

Analytics in Excel – This Excel lecture covers the data analytics options available in Excel such as Regression, Solving linear programming problem (Minimization or Maximization problems), What-if (Goal Seek and Scenario Manager)

Macros – This lecture covers the process of recording a Macro, running a Macro and creating a button to run a Macro.

Bonus Lectures – Waterfall chart in Excel 2016 and previous versions of Excel, Infographics 1: Cool charts, Infographics 2: Cool charts

And so much more!

By the end of this course, your confidence in using Excel will soar. You’ll have a thorough understanding of how to use Microsoft Excel for study or as a career opportunity.

Go ahead and click the enroll button, and I’ll see you in lesson 1!

Cheers

Start-Tech Academy

FAQ’s

Why learn Microsoft Excel?

1. Microsoft Excel helps solve Business Problems

2. Microsoft Excel helps you get stuff done

3. Microsoft Excel will make you better at your job (no matter what that is)

4. Microsoft Excel know-how can instantly increase your job prospects as well as your starting salary

How much time does it take to learn Microsoft Excel?

Microsoft Excel is easy but no one can determine the learning time it takes. It totally depends on you. The method we adopted to help you learn Microsoft Excel quickly starts from the basics and takes you to advanced level within hours. You can follow the same, but remember you can learn nothing without practicing it. Practice is the only way to learn Microsoft Excel quickly.

What are the steps I should follow to learn Microsoft Excel?

1. Start learning from the basics of Microsoft Excel. The first 3 sections of the course cover the basics.

2. Once done with the basic try your hands on advanced MS Excel. Next 7 sections cover Advanced Excel topics

3. Next section will help you some cool new tricks of Microsoft Excel.

4. Practice your learning on the exercise provided with every lecture.

What is the difference between basic and advanced level of Excel?

At Basic level of MS Excel a person

Can build excel formulas using: SUM, IF, AVERAGE, COUNT, ROUND

Is comfortable building excel formulas to manipulate text and dates

Understands and can use the Filter and Sort feature of Microsoft Excel.

Can create basic charts like Line chart, bar chart and pie chart

At Advanced level of MS Excel a person

Can implement Excel LOOKUP Formulas like VLOOKUP, HLOOKUP, Index and Match

Can use conditional and logical formulas like IF, SUMIF, COUNTIF, OR, AND etc.

Knows what a Pivot Table is and how to build one.

Knows what an add-in is and how to install one.

Can record a macro and use it later.

Can successfully edit/modify simple recorded macros.

Can create advanced charts like Waterfall chart and overlay chart in Microsoft Excel

Can create solve analytics problem using excel solver.

Start working proficiently on Microsoft Excel and increase your office productivity.

The Authors of this course have several years of corporate experience and hence have curated the course material keeping in mind the requirement of Excel in today’s corporate world.

Who this course is for:

Working Professionals beginning their Data journey

Anyone curious to master excel from beginner to Advanced in short span of time

Students about to join their first corporate

Size: 1GB

DOWNLOAD TUTORIAL

The post MICROSOFT EXCEL MASTERCLASS FOR BUSINESS MANAGERS appeared first on GetFreeCourses.Me.

#IFTTT #Blogger

0 notes

access-excel-tip · 6 years ago

Text

New Post has been published on Access-Excel.Tips

New Post has been published on https://access-excel.tips/excel-pivot-table-distinct-count-unique/

Excel Pivot Table Distinct Count to count unique value

This Excel tutorial explains how to use distinct count in Pivot Table to count number of unique value in a column grouped by other fields.

Excel Pivot Table Distinct Count to count unique data

In Excel 2013, there is a new aggregate function in Pivot Table called Distinct Count, which counts number of unique value in a column. For example, if a column contains employee names, you can use the distinct count function to count number of unique employee names in the column such as below.

In this tutorial, I am going to demonstrate how to do distinct count.

Example – distinct count number of unique employee names by department

Suppose we have a staff list below. We want to see how many unique employee names are in the same department.

Select the concerned data, navigate to Insert > Pivot Table, then in the Create PivotTable dialog, check the box Add this data to the Data Model > OK

This option is very important as Distinct Count function will not be available if you don’t check this box.

In the Pivot Table, drag Department and Employee Name to the Rows, drag Employee Name to the Values. By default, the aggregate function on the value is Count.

Click on the arrow next to Count of Employee Name, select Value Field Settings

In the Value Field Settings, select Distinct Count > OK

Now the Pivot Table displays the distinct count of employee name by department and display each all the names under each department.

Overall, there are a total of 17 staff, as there are two Cathy and two Icy, the distinct count of employee name in the whole company is 15.

Alternatively, display the distinct count without displaying the employee name.

Outbound References

https://support.office.com/en-us/article/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273

0 notes

templified · 6 years ago

Text

Pivot, Simple, User Friendly Photo Portfolio Theme | Templified

New Post has been published on https://templified.com/pivot/

Pivot, Simple, User Friendly Photo Portfolio Theme

That image is from the front page of the creative or agency theme demo. It’s just one of dozens of great looking demo sites to help give you an idea of what Pivot can bring to the table.

The Pivot WordPress theme offers an excellent alternative for truly customized web pages to creative professionals. Any website made together with it will appreciate a modern and fashionable construction that can showcase photography, art or any other jobs or products mandatory. The website is shown upon since it uses Twitter Bootstrap v3 and suitable CSS3 and JS programming, everything loads fast regardless of what size or resolution display. This theme that is open offers endless structural choices without a knowledge of development or programming required. The superior website-construction plugin Visual Live Composer is.

Here’s the premium business page demo. For more business themes, check this collection out. Or maybe you’re interested in some great individual WordPress business themes? Three I really like are Kwoon. Scalia. The Agency.

Here’s a high quality education web template concept. More education themes are in our collection.

For launching apps, you could do a lot worse than Pivot. What other app themes are there? Well, look no further than this collection. Perfect for any website or app launch.

And the classic homepage look. Some people never get tired of Edison lightbulbs. I love this flat design. More awesome flat themes here.

Bring attention to you fabulous website from around the world with WPML integration and complete translation abilities. Borders mean nothing to a fantastic theme like Pivot, so you should absolutely use this capability to the utmost, ensuring your site will be popular on every continent. Before you add these five extras all, each page has to be designed, which can be easily accomplished with teh Visual Composer drag and drop page builder, included for no extra charge. In addition, it comprises eight creative fashions for headers and an infinite number of colors and background choices to pick from.

Pivot is a fantastic theme for just about any sort of business, so you should feel confident if this is the one you choose.

Pivot Offers an Awesome List of Features Along With Massive Compatibility!

Pivot is compatible with so many add-ons and plugins, and the list is constantly growing. Want to know about your favourite plugin? Just ask! Currently compatible with:

Revolution Slider (Included with Purchase, Save $17)

Master Slider (Included with Purchase, Save $28!)

Ebor Page Builder

WooCommerce

WPML – Multilingual WordPress

Contact Form 7

Gravity Forms

Easy Digital Downloads

WP Job Manager

8 Header Types – Pivot gives you a wide variety of looks with 8 different header layouts to choose from currently, check them out!

Ebor Mega Menu – Bring me a brain Ebor! Nope, wrong thing. Ebor Mega Menu is built straight into the theme, use all the page builder elements right there in your menu, or just use it to add richer content to your header along with columns of menus, how you use it is entirely up to you!

WooCommerce Support – You know you love WooCommerce as much as I do. Pivot supports WooCommerce, we’ve baked in beautiful styling for it, and custom goodies like a beautiful AJAX updating cart icon in the header, neat!

Custom Image Cropping – Great for photographers. Sick of not being able to choose the crop area of your featured images? Not in Pivot, you have total control of where your featured images crop.

Ebor Template Builder – It’s great to have the flexibility to create just what you want. That’s what a page builder is all about. Love Aqua Page builder, but feel it’s missing some features? My custom build adds them. Markup buttons, template duplication, quick view list for templates and loads more. It’s Aqua Page Builder on steroids.

30+ Custom Page Builder Elements & Counting! – They just keep adding to this and I love every minute of it. The page builder in Pivot comes packed full of features that are totally customisable, to extend that we’ve hand designed and coded another 30+ element for the page builder, just for Pivot! Full blog feeds, portfolio feeds, carousels, you name it! Best of all, each element is totally customisable, show only certain categories in a page, define background images, everything!

Parallax Background Image Sections – You can set parallax background images on a section by section basis when building your pages. With parallax, aAnything is possible.

Revolution Slider Included and Master Slider Also Included!– Revolution slider is included with your download, which is great since it even integrates with the page builder! Full demo data of our demo slider is also included. Master Slider adds even more great functionality. Which is your favorite?

Custom Logos – Pivot lets you set a custom logo, retina logo, and even a logo for the WP Login screen, all with a few clicks in our beautiful live preview theme options. Even our theme options let you see your theme changes in real time!

Unlimited Color Schemes, 600+ Google Fonts – Pivot gives you full control over the theme colours, using our live preview theme options you can change all the colors in the theme, you’re given total control over the theme fonts, sizes, paddings, margins, you name it!.

One Click Demo Data Installer – With my custom wordpress data importer, your site can be up and running like my demo site in seconds.

WPML Support – Full multilingual support is baked right into Pivot! Full .po file available for more simple translations also.

Gravity Forms & Contact Form 7 Support – Pivot is totally ready for Gravity Forms straight out of the box, start building the forms you need straight away. If you’d prefer a free contact form builder, we also support Contact Form 7 in Pivot!

1, 2, 3 or 4 columns footer – It’s all up to you! the theme will respond to how you’re using the footer widgets areas and build the columns with no extra work from you!

WordPress Post Formats Support – Our posts use post formats to produce beautiful layouts depending on what you’re showing. Each with beautiful, easy to use controls to make your content shine!

Built in sharing – We’ve built beautifully styled sharing buttons right into the theme, share your content with style.

500+ Icons – Beautiful, retina ready icons at your disposal, use them all over the theme, they look beautiful on mobile devices too!

Blog Layouts – Grid Blog, Classic Blog and all again with a sidebar. You’ve got 4 different blog layouts to choose from, and with the included page builder, you can even change this on a page by page basis! Want one layout for one category, and other layout for another category, no problem!

Portfolio Post Type Layouts – Pick a post format, pick a post layout, mix and match to make a portfolio post that truly suits your content. Feature images, gallery, sound cloud, video, anything you need! then let your users share it with our beautiful sharing buttons.

Child theme included! – To make life easier I’ve included a basic child theme for you to install. Get your modifications going even faster!

5* Support, All From the Developer – I run all my own support, so you’re guaranteed a great & quick answer to your theme usage or setup question.

Key Features

WP 4.2.2+ Ready and Tested & 4.4+ ready & 4.8+ ready

One Click Demo Install

Amazing Ebor Page Builder Included

Lightning Fast

Fully Localized and Translation Ready

Flat design

Revolution Slider Included ($16 value)

Master Slider Included

%100 Fully Responsive

Twitter Bootstrap 3.2

Parallax effect

Infinite Homepage Layouts

Retina-ready graphics and icons

SEO-friendly

Portfolio posts types

Filterable portfolio

Google Fonts

Rich styling options

Contact form 7 ready

Gravity Forms Ready

Help documentation

jQuery enhanced

Cross browser compatibility

Okay, that just about does it for Pivot. We hope you’ve enjoyed this brief rundown and we hope you come back for more.

Demo Get Hosting

#Portfolio

0 notes

file-formats-programming · 8 years ago

Text

Tile Picture as a Texture inside Shape & Use Formula Parameter in Smart Markers using .NET

What’s new in this release?

Aspose development team is pleased to announce the new release of Aspose.Cells for .NET 17.6.0. This release includes a few new features and other enhancements along with some critical bug fixes that further improve the overall stability of the APIs. Aspose.Cells supports image tiling feature which allows users to display images that are too large to be displayed entirely as a single unit on a typical computer. The feature allows developers to display by segmenting it into smaller, more manageable image tiles. Sometimes, developers need to embed formula in Smart Markers. Aspose.Cells allows developers to make use of Formula parameter in Smart marker field. OnAjaxCallFinishedClientFunction is a client side function which is called when user copies some data into Aspose.Cells.GridWeb worksheet. This function is helpful when bulk of cells are updated and users want to keep track of those updated cells at client side (i.e., in web browsers like FireFox, Google Chrome etc.). Aspose.Cells 17.6 has enhanced its core for more stability as well as fixed many critical bugs. Moreover, there are numerous minor enhancements in order to improve the overall usage of the API. A few of the worth mentioning enhancements includes, Enquote all attribute values in HTML, Support some new functions of Office 365, Remove unnecessary older Web.config settings of Aspose.Cells.GridWeb like force script_path and forcepath, Group Rows and Create Subtotal – Aspose.Cells.GridWeb, and Handled an exception, “Input string was not in a correct format” when loading an XLSX file format. Furthermore, in this release, we have fixed several other issues. For example, issues around reading/writing MS Excel file formats, data sorting, data validation, rendering shapes and drawing objects, rendering and manipulating charts, rendering HTML to Excel and vice versa, rendering images from Excel worksheets, rendering images files from charts and exporting Excel workbooks to PDF format have been resolved. This release includes several enhanced features and bug fixes as listed below

Enquote all attribute values in HTML

Support new functions of Office 365

Support Table as the data source of the chart

Remove older Web.config settings of Aspose.Cells.GridWeb like force script_path and forcepath

Exception message is wrong - The count of Hyperlinks cannot be larger than 66530

Excel footer in the output PDF is not italic - it should be Arial 9 and italic

Copy the PageSetup settings from the existing worksheet

Some part of subscript labels are cut and shifted to a lower line

Subscript text is displaced to the right a bit - Excel to PDF Rendering

Output Excel file is not good when converting an HTML file to Excel file format

Exception thrown after multiple loading and saving an XLS file

Load Filter causes crash while loading an Excel file

Using IFERROR with NPER is unable to be processed correctly

Output PDF should have one page instead of two pages

Image rendered incorrectly when a cell has Arabic currency name

Shape of parentheses is rendered wrong in output PDF/image

Aspose.Cells saved PDF is not like MS Excel saved PDF

Sparklines not displayed properly when rendered as image

Incorrect legend item colors when BankActionRatPie chart is converted to image

Incorrect legend item colors when TotalActionRatPie chart is converted to image

Aspose.Cells values start from 10.0% to 14.0%

Aspose.Cells shows values 0

DataLabels in the chart are not shown or removed in the output file

Generated chart's image is incorrect

Custom sorting is lost when saved to XLS by Aspose.Cells APIs

Data sorting is not working on DataSet (having formulas) in the worksheet

Cell border issue in Range.ApplyStyle() method

An embedded object in Sheet2 missing and scaling is wrong

Data validations end up strange after inserting new columns

Data validation issue - Value1 contains a "?"

Cells.DeleteBlankColumns() removes embedded objects in the worksheet

Referencing a column with a header starting with hash sign ('#') but without "@" sign

Opening and saving the XLSB file corrupts it

Unexpected row inserted after Smart Makers in nested list

Hash char ("#") in the table headers and referencing it in other cell

Space in the end of table headers and referencing it in other cell

Cell.GetValidationValue returns incorrect value when using custom data validation formula

Hyperlink in 3rd worksheet is not identified

Exception on opening the template XLS file via Aspose.Cells APIs

When adding rows near a ListObject containing formulas, those formulae are wrongly duplicated upward

The text format changed (spacing increased) after loading and saving the XLS

PrintObject property of command buttons is set to true after loading and saving an XLSM file

The cell formatting lost after rendering to PDF format

XLSX file is corrupted after changing background image of a chart

Inserting ranges also extend the size of the chart above

ListObject.ConvertToRange breaks formulas in the totals row

Some features are not working

C1 worksheet is not fully loaded and GridWeb shows only 64 rows instead of 75

Collapse all the groups of I. 100 worksheet will create an empty space under the 64th row

Scrolling down control worksheet makes GridWeb to becomes empty

Vertical scroll bar is lost after expanding the rows

Other most recent bug fixes are also included in this release

Newly added documentation pages and articles

Some new tips and articles have now been added into Aspose.Cells for .NET documentation that may guide users briefly how to use Aspose.Cells for performing different tasks like the followings.

Using Formula parameter in Smart Marker field

Tile Picture as a Texture inside the Shape

Overview: Aspose.Cells for .NET

Aspose.Cells is a .NET component for spreadsheet reporting without using Microsoft Excel. It supports robust formula calculation engine, pivot tables, VBA, workbook encryption, named ranges, spreadsheet formatting, drawing objects like images, OLE objects and importing or creating charts. You can also create Excel file using designer spreadsheet, smart marker or API and apply formulae and functions. It supports CSV, SpreadsheetML, PDF and all file formats from Excel 97 to Excel 2007.

More about Aspose.Cells for .NET

Homepage of Aspose.Cells for .NET C#

Download Aspose.Cells for .NET

#Formula parameter in Smart Marker field #Tile Picture as Texture in Shape #Use Formula parameter in Smart Markers #exporting Excel workbooks to PDF #.NET Excel API #writing MS Excel file formats #rendering shapes and drawing objects

0 notes

hotspreadpage · 8 years ago

Text

Does your website suffer from one of these five content gaps?

In 2016, Google rolled Panda into its core algorithm. What this means for webmasters is that a website can be hit by (and recover from) a content penalty at any time.

But, more problematically, it also means it’s becoming impossible to diagnose why a website has dropped in rankings. Google ultimately does not want us to understand how its ranking algorithm works, because there will always be people who manipulate it. We now suspect that core signals are rolled out so slowly that SEOs won’t even realise when Penguin or Panda has refreshed.

For this reason, it makes it crucial that we understand how well our website is performing at all times. This blog post is intended to show you how to do a comprehensive content audit at scale, in order to find any gaps which may lead to rankings penalties.

Essentially, there are five types of content gaps a website may suffer from. I’ll explain each one, and show you how you can find every instance of it occurring on your website FAST.

1. Internally duplicated content

Internally duplicated content is the daddy of content gaps. Duplicating optimised content across multiple pages will cause cannibalisation issues, wherein Google will not know which internal page to rank for the term. The pages will compete for ranking signals with each other, reducing the rankings as a result.

Further to this, if you have enough duplicate content within a directory on your site, Google will treat that entire directory as low quality and penalise the rankings. Should the content be hosted on the root, your site’s entire rankings are under threat.

To find these at scale you have to use Screaming Frog’s custom extraction configuration to pull all your content from your site, then compare in Excel for duplicates. Using this method, I was able to find 6,000 duplicate pages on a site within a few hours.

To configure Screaming Frog you first need to copy the CSS selector for your content blocks on all your pages. This should be relatively simple should your pages follow a consistent template.

Go onto the page, right click on the content and go to inspect element. This will open up the right panel at the exact attribute which you right clicked on. From there, right click again, but on the attribute, go to copy, then select copy selector:

From here you need to go into Screaming Frog. Go into the configuration drop down and select custom, then extraction.

From here this will bring up the below box, which you will then need to select CSSPath as the mode, and then paste your selector into the highlighted field, and change the second drop down to extract text.

Now if I run a crawl on the ASOS website, Screaming Frog is going to extract the above the fold content from all the category pages.

You can specify up to 10 separate paths to extract, so when you have templates with multiple content blocks, for example above and below the products, or category and product & static page templates, you will need to specify them in the same way I have just shown.

Now, it is going to be very unlikely that an entire page’s content, or an entire block of content, will be duplicated. Usually spun content keeps the majority of the content the same but will replace specific keywords. So trying to find duplicate content based on entire blocks of content is pretty futile. Luckily I have created a duplicate content tool for this exact situation.

Fire up the tool and input just the URL and content into the specified columns on the input tab. Essentially what we have to do is split the content down into sentences and compare occurrences this way.

The output will only work if you have a single content column, so if you have had to extract multiple content blocks on each URL simply combine them before you paste into the tool. You can do this with the concatenate function which for example would look like this (should your content be in columns A2 & B2).

=concatenate(A2, “.”, “B2”)

The period in the middle is essential (if your content does not contain periods at the end) because we are going to split the content out by text to columns by full sentences, so will use the period as the delimiter.

Next highlight the column with the content in it, then select text to columns. Select delimited, then select other and specify a period as the delimiter.

Once you have done this you will need to amend the formula in column A3 depending on how many columns of delimited text you have.

In my example I have ten columns so I will edit the formula to display as this:

=A2+10

Drag the formula down so we’re adding ten onto the figure of the preceding cell. This step is necessary as it allows us to group our delimited content by URL on the Output sheet.

From here your Output sheet will auto populate. If you’ve got over 100,000 rows of data, you will need to drag the formulas down until you get errors.

From here I would lock down the formulas by pasting as special to speed up the spreadsheet. Then clear up the Output sheet by removing all errors and 0s.

Finally go into the pivot table spreadsheet and refresh the table to show all URLs sorted by highest amount of duplicate occurrences. You can expand the URL to see exactly which sentences are being duplicated.

In this example I found a ton of pages (6,927 pages) which had heavily duplicated each other within a couple of hours total.

2. Externally Duplicated Content

Externally duplicated content is content which has either been purposefully syndicated on multiple websites, or scraped by malicious web robots. A prime example of externally duplicated content is companies copying manufacturers descriptions instead of writing something unique.

When a search engine discovers content which has been duplicated across multiple websites, it will often work out the originator of the content and then throw the rest out of its index. Normally it is pretty good at this, but if our website has issues will crawl budget, or gets its content syndicated by a website with far greater authority, there is a chance that Google will show them over us.

Finding externally duplicated content begins much in the same was as before. Extract your content from the website and use the download to split the data out into individual sentences.

Next, we just need to block quote some of the data and search it into Google (use concatenate to quickly put all your data into block quotes). To automate this process, we have our own tool which can do this for thousands of searches at a time. A good free alternative would be to use URL profiler’s simple SERP scraper.

What you’re essentially doing here is performing a search for a block of your content in Google. If your site doesn’t show up in P.1 for this, then you have a big problem.

Search Engine Watch is heavily scraped. Here I have done a search for a bit of a previous blog post of mine, and Google have found 272 results!

Search Engine Watch is still in first position, which is not an issue, but should you find instances where you are outranked, you’re going to have to rewrite that content.

3. Content Gaps Across Devices

The impending mobile first algorithm means this issue should be at the forefront of everyone’s minds. Google have publicly stated that desktop websites will be judged on the content which is displayed on their mobile site first. This means if we have mobile pages without content where it appears on desktop, we’re going to take a hit to our rankings.

To find content gaps it’s the same process as before. You should already have crawled all of your pages from a desktop point of view. Now you just need to go through all the templates again, but in mobile view.

Fire up the webpage and go into inspect element and change device to mobile:

Go into the body of content and copy the selectors in the same way as before and run a crawl on all your URLs again.

Once you are done you can compare content side by side in Excel for gaps. Remember to concatenate your data should you have multiple selectors per page.

Put them in an Excel doc and a simple function will help you spot gaps instantly.

Here all I did was the following function and I’ve got some results instantly:

=IF(B2=C2, “Match”, “No Match”)

The consensus now seems to be that accordions are okay on mobile, so don’t worry about compromising your design to get all your content in. Just make sure it is all there on the page.

4. Thin content

Thin content is just as big a concern as duplicate content for the obvious reasons. Without a significant amount of valuable content on a page Google will not be able to understand the topic of the page and so the page will struggle to rank for anything at all. Furthermore, how can a webpage claim to be an authority on a topic if it does not contain any information on it? Google needs content in order to rank a page, this is SEO 101!

Luckily, we are already 90% of the way there to diagnosing all our thin content pages already. If you have completed steps 1-3 you’ll already have the content on your mobile and desktop website by URL.

Now we just need to copy in this formula, changing the cell reference depending on what we’re analysing:

=IF(LEN(TRIM(B2))=0,0,LEN(TRIM(B2))-LEN(SUBSTITUTE(B2,” “,””))+1)

This will give us the word count of the URL (for both mobile and desktop). Then go through and raise any pages with less than 300 words as requiring additional content.

5. Content above the fold on page load

The final type of content gap is a massive bugbear of mine. It is estimated that 80% of our attention is captured by the section of a webpage that is visible on page load.

Google understands that content buried at the bottom of a page is probably never going to get read. As a result, they do not give as much weighting to the content here. No matter how much valuable content you have on a page, if a significant chunk of this is not visible on page load then this is a wasted effort.

In order to diagnose how much above the fold content we have, we will need to rerun our crawls, but this time we only want to extract the content blocks which are visible on page load. From here, just running the above word count formula will be sufficient to diagnose content gaps.

There you have it. What do you do with duplicate/thin content pages once you discover them? I’d recommend every URL should have at least 200-300 words of unique, valuable content on it, with at least 50-100 words appearing above the fold. If you cannot produce that amount of valuable information on the page, then either the page should not exist or the page should not be indexable.

Does your website suffer from one of these five content gaps? syndicated from http://ift.tt/2maPRjm

0 notes

kellykperez · 8 years ago

Text

Does your website suffer from one of these five content gaps?

In 2016, Google rolled Panda into its core algorithm. What this means for webmasters is that a website can be hit by (and recover from) a content penalty at any time.

Essentially, there are five types of content gaps a website may suffer from. I’ll explain each one, and show you how you can find every instance of it occurring on your website FAST.

1. Internally duplicated content

To configure Screaming Frog you first need to copy the CSS selector for your content blocks on all your pages. This should be relatively simple should your pages follow a consistent template.

From here you need to go into Screaming Frog. Go into the configuration drop down and select custom, then extraction.

Now if I run a crawl on the ASOS website, Screaming Frog is going to extract the above the fold content from all the category pages.

=concatenate(A2, “.”, “B2”)

Next highlight the column with the content in it, then select text to columns. Select delimited, then select other and specify a period as the delimiter.

Once you have done this you will need to amend the formula in column A3 depending on how many columns of delimited text you have.

In my example I have ten columns so I will edit the formula to display as this:

=A2+10

Drag the formula down so we’re adding ten onto the figure of the preceding cell. This step is necessary as it allows us to group our delimited content by URL on the Output sheet.

From here your Output sheet will auto populate. If you’ve got over 100,000 rows of data, you will need to drag the formulas down until you get errors.

From here I would lock down the formulas by pasting as special to speed up the spreadsheet. Then clear up the Output sheet by removing all errors and 0s.

In this example I found a ton of pages (6,927 pages) which had heavily duplicated each other within a couple of hours total.

2. Externally Duplicated Content

Finding externally duplicated content begins much in the same was as before. Extract your content from the website and use the download to split the data out into individual sentences.

What you’re essentially doing here is performing a search for a block of your content in Google. If your site doesn’t show up in P.1 for this, then you have a big problem.

Search Engine Watch is heavily scraped. Here I have done a search for a bit of a previous blog post of mine, and Google have found 272 results!

Search Engine Watch is still in first position, which is not an issue, but should you find instances where you are outranked, you’re going to have to rewrite that content.

If you’d like to access the duplicate content calendar straight away simply click on the button below:

3. Content Gaps Across Devices

Fire up the webpage and go into inspect element and change device to mobile:

Go into the body of content and copy the selectors in the same way as before and run a crawl on all your URLs again.

Once you are done you can compare content side by side in Excel for gaps. Remember to concatenate your data should you have multiple selectors per page.

Put them in an Excel doc and a simple function will help you spot gaps instantly.

Here all I did was the following function and I’ve got some results instantly:

=IF(B2=C2, “Match”, “No Match”)

The consensus now seems to be that accordions are okay on mobile, so don’t worry about compromising your design to get all your content in. Just make sure it is all there on the page.

4. Thin content

Now we just need to copy in this formula, changing the cell reference depending on what we’re analysing:

=IF(LEN(TRIM(B2))=0,0,LEN(TRIM(B2))-LEN(SUBSTITUTE(B2,” “,””))+1)

This will give us the word count of the URL (for both mobile and desktop). Then go through and raise any pages with less than 300 words as requiring additional content.

5. Content above the fold on page load

The final type of content gap is a massive bugbear of mine. It is estimated that 80% of our attention is captured by the section of a webpage that is visible on page load.

from IM Tips And Tricks https://searchenginewatch.com/2017/02/16/does-your-website-suffer-from-one-of-these-five-content-gaps/

0 notes

sheilalmartinia · 8 years ago

Text

Does your website suffer from one of these five content gaps?

In 2016, Google rolled Panda into its core algorithm. What this means for webmasters is that a website can be hit by (and recover from) a content penalty at any time.

Essentially, there are five types of content gaps a website may suffer from. I’ll explain each one, and show you how you can find every instance of it occurring on your website FAST.

1. Internally duplicated content

To configure Screaming Frog you first need to copy the CSS selector for your content blocks on all your pages. This should be relatively simple should your pages follow a consistent template.

From here you need to go into Screaming Frog. Go into the configuration drop down and select custom, then extraction.

Now if I run a crawl on the ASOS website, Screaming Frog is going to extract the above the fold content from all the category pages.

=concatenate(A2, “.”, “B2”)

Next highlight the column with the content in it, then select text to columns. Select delimited, then select other and specify a period as the delimiter.

Once you have done this you will need to amend the formula in column A3 depending on how many columns of delimited text you have.

In my example I have ten columns so I will edit the formula to display as this:

=A2+10

Drag the formula down so we’re adding ten onto the figure of the preceding cell. This step is necessary as it allows us to group our delimited content by URL on the Output sheet.

From here your Output sheet will auto populate. If you’ve got over 100,000 rows of data, you will need to drag the formulas down until you get errors.

From here I would lock down the formulas by pasting as special to speed up the spreadsheet. Then clear up the Output sheet by removing all errors and 0s.

In this example I found a ton of pages (6,927 pages) which had heavily duplicated each other within a couple of hours total.

2. Externally Duplicated Content

Finding externally duplicated content begins much in the same was as before. Extract your content from the website and use the download to split the data out into individual sentences.

What you’re essentially doing here is performing a search for a block of your content in Google. If your site doesn’t show up in P.1 for this, then you have a big problem.

Search Engine Watch is heavily scraped. Here I have done a search for a bit of a previous blog post of mine, and Google have found 272 results!

Search Engine Watch is still in first position, which is not an issue, but should you find instances where you are outranked, you’re going to have to rewrite that content.

If you’d like to access the duplicate content calendar straight away simply click on the button below:

3. Content Gaps Across Devices

Fire up the webpage and go into inspect element and change device to mobile:

Go into the body of content and copy the selectors in the same way as before and run a crawl on all your URLs again.

Once you are done you can compare content side by side in Excel for gaps. Remember to concatenate your data should you have multiple selectors per page.

Put them in an Excel doc and a simple function will help you spot gaps instantly.

Here all I did was the following function and I’ve got some results instantly:

=IF(B2=C2, “Match”, “No Match”)

The consensus now seems to be that accordions are okay on mobile, so don’t worry about compromising your design to get all your content in. Just make sure it is all there on the page.

4. Thin content

Now we just need to copy in this formula, changing the cell reference depending on what we’re analysing:

=IF(LEN(TRIM(B2))=0,0,LEN(TRIM(B2))-LEN(SUBSTITUTE(B2,” “,””))+1)

This will give us the word count of the URL (for both mobile and desktop). Then go through and raise any pages with less than 300 words as requiring additional content.

5. Content above the fold on page load

The final type of content gap is a massive bugbear of mine. It is estimated that 80% of our attention is captured by the section of a webpage that is visible on page load.

from Search Engine Watch https://searchenginewatch.com/2017/02/16/does-your-website-suffer-from-one-of-these-five-content-gaps/