This template allows you to pull data from Moz Open Site Explorer (OSE) into Excel using Power Query (Get & Transform in Excel 2016). Moz OSE is an online tool that allows you to see what sites link to a given site (known as a backlink). Links to a site is one of the top ranking factors for search engines like Google. More backlinks from higher quality sites means higher search ranking!...
If you are creating an Excel spreadsheet for other users to input data, then dropdown lists are very useful to control what data they are entering. This way you can ensure that they will not enter incorrect data which will produce errors in your spreadsheet when calculations are made based on the user input. Dropdown lists should be familiar as you will frequently find them on the web or while working in other applications....
images play an important role in conveying information. But, for people with visual impairments, images can be inaccessible. This is why alt text is so important. Alt text is a brief description of an image that can be read by screen-reading software. It allows people with vision impairments to understand the images in your workbook. Including alt text is essential for making sure that all users can access the content of your spreadsheet....
You can download the example workbook using the above link. Example This formula tests the date contained in cell B3 to determine if it’s a weekend or weekday. Generic Formula Date is the date which you want to test whether it’s a weekend or not. The formula will return true if the date is a weekend and will return false if it’s a weekday. What It Does This formula will test a given date to see if it’s a weekend (Saturday or Sunday) and return TRUE if it is a weekend and FALSE if it is not a weekend....
The Ctrl + Z (for undo) and Ctrl + Y (for redo) keyboard shortcuts.The undo and redo buttons in the Quick Access Toolbar. But did you know about Excel’s feature to undo and redo multiple actions at one time? The same thing can be done with redoing actions.
These are the top people, websites, YouTube channels, and Forums you’re going to want to start following. When trying to learn new skills it’s essential to immerse yourself in the topic, and these resources will be a great start! Obviously, you won’t be able to follow all of these, but you should be able to find a few great learning resources you really enjoy. Are you the proud owner of one of these amazing Microsoft Excel resources?...
But they have a lot of drawbacks that make them a not so great option. In this post, I’ll show you everything you need to know about merged cells including 8 ways to merge cells. I’ll also tell you why you shouldn’t use them and a better alternative that will produce the same visual result. What is a Merged Cell A merged cell in Excel combines two or more cells into one large cell....
Example Generic Formula What It Does This formula will calculate the area of a circle given its radius. How It Works The area of a circle is given by PiRadius^2 where Pi is a constant approximately equal to 3.14159265. Excel has this constant built in as a function with no parameter inputs PI(). The POWER function will take any number and raise it to the power of any other number. POWER(Radius,2) will return the square of the Radius....
We use 1ISTEXT to return a 1 for any text strings and 0 for #N/A errors. We then SUM the result up and this is our count of cells containing a formula. In our example FORMULATEXT(Range) results in the following array. ISTEXT({“=1+2″;#N/A;”=CHOOSE(1,”Hi”,”Bye”)”;”=PI()”;”=ISODD(7)”}) will return an array with TRUE values when there is a text string and FALSE values when there is an #N/A error value. In our example this results in {TRUE;FALSE;TRUE;TRUE;TRUE}....
Get the example workbook with the above link to follow along. In this example, you have a table of sales figures each in a separate tab named Jan through Dec. Each sheet is the same format with the table in the same position within each sheet. If you wanted to create a Total sheet and have a table in it that sums up each of the tables in the Jan to Dec sheets, then you could use the above formula and copy it across the whole table....
Solver is an Excel add-in that helps you perform what-if type analysis. You can find optimal outcomes subject to given constraints. For example, you could use Solver to find the maximum profit based on your financial models. The Solver add-in works by iterating through many scenarios until it finds the most optimal outcome. Solver is an indispensable tool for any sort of optimization problem in Excel. This post is going to show you how to get the Solver add-in for Excel....
Doing this manually would be a very tedious task. This is not really the kind of task I find fun, and usually prompts me to think “how can I avoid doing this?“. Since I already had my tip titles listed in an Excel sheet, this was the perfect task for a little bit of VBA. Creating the Folders from a List I set up a named range in my sheet called MakeFolderPath as an input for a folder path....
Note: You will need to save the completed workbook somewhere on your computer for the functions to work. How do you get information about the current workbook such as the file name, file path and the current sheet name? The CELL function can be a pretty handy tool for getting this type of information, but you’ll have to do a bit of work to get it. Without any adjustments CELL(“filename”,A1) will return the full file path and current sheet name, so some text parsing will be needed....
Excel has gridlines in each sheet or your workbook. These are the light gray lines that outline each cell in the sheet. Gridlines can help you distinguish between the rows, columns, and any data they contain. But maybe you don’t want to see the gridlines in your final report or dashboard. In this case, you will want to hide these gridlines. Removing the gridlines is very easy and it can be done in many ways....
In this example I’ve set up two lists and created tables from them called List1 and List2. Create a Connection Only Query for the Two Lists For each table, we’re going to create a connection only query that adds a column with the same constant value in each row. We will then use this column to join our two tables together using a merge query. Select a cell in your list and then go to the Data tab and select the From Table/Range command in the Get & Transform Data section....
Method 1: Advanced Filters The Advanced Filter has a great feature that allows you to copy a list of unique values from a range you specify to another location. Now your list of unique values will appear starting in the cell you selected. Notice that even format is copied over. Method 2: Remove Duplicates Excel has a very useful feature called remove duplicates which can be used on either the whole data set or a single column of data....
Entering and storing data is a common task in Excel. If this is something you’re doing, then you need to use a table. Tables are containers for your data! They help you keep all your related data together and organized. Tables have a lot of great features and work well with other tools inside and outside of Excel, so you should definitely be using them with your data. This post is going to show you all the ways you can create a table from your data in Excel....
But what if your data format keeps changing? Then you either need to change the query to adapt to the new format or change the source data files before they get consumed by power query. Both options aren’t ideal since the whole point of using power query is to automate the whole data transformation process. I recently came across this in my work, in order to get things done I changed the data each time at first....
Is your text data not fully visible or your numerical data appears as #’s? Then here’s a quick way to expand columns in Excel to the exact width needed to view all the data. If the columns you want to select are not adjacent, you can add to your selection by holding ctrl and left clicking the column heading.
Remember, if you’re not using Excel 2016 or later, then you’ll need to install the power query add-in. Data to Extract In this post we’re going to take a look at how we can pull data from a series of similar pages. I’m a big MMA fan, so the example we’re going to look at is getting a list of all UFC results from Wikipedia. If you visit the Wikipedia page for UFC events there’s a table of Past Events....
