Posts

Change the default font to Wingdings. Close Excel and then the next time they open a new workbook in Excel they’ll be greeted with a load of Wingdings. Oh no, Excel is broken!

Understanding the gold commodity market requires constant vigilance. This is often something market watchers want to track in Excel, but it’s not obvious this can be done in Excel without a manual process. Excel does in fact offer methods to track commodity prices such as gold. This helps make tracking gold prices much more accessible and enables investors to assess market trends and make informed decisions. This post is going to show you how to get the current and historical price per ounce of gold in your Excel spreadsheet....

Syntax ABS(Number) Number (required) – This is the number you want to find the absolute value of. Example In this example find the absolute value of a few numbers. Text values will result in a #VALUE! error.

Change the default number of sheets in a new workbook.

Example Generic Formula What It Does This formula will create a hyperlink that opens a file explorer window to the folder location where the current workbook is saved. How It Works LEFT(CELL(“filename”,C2),FIND(“[“,CELL(“filename”,C2))-2) will return the folder path of the current workbook. See how this part of the formula works from another post. In this example, this part of the formula returns “C:\Users\John\Google Drive – Excel\Excel Website\Formulas\How To Create A Hyperlink To The Current Workbook Folder“....

Step 1: Add a table style. Adding a table style is a quick and easy way to make your data stand out. Like magic, your table is perfectly styled and already much more appealing. Step 2: Add visualization to your data with in cell data bars. Now that the table is styled, let’s add some visualization. No one like looking at numbers when can see a picture instead. Repeat the above steps while this time only highlighting the Totals....

You have a list of names and it’s all lower case letter. You need to fix them so they are all properly capitalized. With hundreds of names in your list, it’s going to be a pain to go through and edit first and last names. Thankfully, there are some easy ways to change the case of any text data in Excel. We can change text to lower case, upper case or proper case where each word is capitalized....

You may find that product references consist of a product code, code reference, and product size all concatenated into one piece of text which appears in one cell within the worksheet. Your requirement is that you want to split the relevant sections of the text string so that they all appear in individual cells in your worksheet. This may be because you want to sort or group by a particular section of text....

When you create a table, Excel will give it a generic name such as Table1, Table2, etc. But this isn’t usually a very good descriptive name for your table. You will need to reference your table name in formulas and other locations, so giving it a descriptive name is the best practice. Get your copy of the example workbook to follow along with the post and find out how to give your table a new name!...

When performing any data analysis in Excel you will often want to know the number of distinct items in a column. This statistic can give you a useful overview of the data and help you spot errors or inconsistencies. This post will show you all the ways you can count the number of distinct items in your list. Get your copy of the example workbook in this post to follow along....

Add your button to a sheet. To re-label the button to something more meaningful than button1, left click on the button and select Edit Text from the menu. You can also re-assign you button from this menu by selecting Assign Macro.

The Analysis Toolpak is an Excel add-in that provides a range of analysis features. It includes tools for regression analysis, correlation, covariance, and many other statistical calculations. The Analysis Toolpak can be a valuable asset in your data analysis arsenal. This post will show you how to install the add-in to your Excel. Install Analysis Toolpak Add-in The Analysis Toolpak add-in is available to all versions of Excel from 2007 onward....

Adding symbols into your tables or charts can be a great visual aid to your numbers. In this example we’ll look at how we can add up and down arrows into our number formatting to show increases or decreases in our data. The symbols we’re going to use are unicode characters which are actually a standard across any platform and are available on any language PC. Step 1: Accessing Symbols in Excel We’ll need to insert the symbols we want to use into our workbook....

Syntax IFERROR(Expression, Value if error) Expression (required) – This is part of the formula that is checked for an error.Value if error (required) – This is the value that is returned if the expression results in an error (#DIV/0, #N/A, #NAME, #NULL!, #NUM!, #REF!, or #VALUE!). Example In this example we want to calculate an average cost per use = cost / # of uses (=A2/B2), however some of the # of uses are 0 and this results in a #DIV/0 error....

This function will allow you to find and replace a piece of text within a text string. You can either replace all occurrences of the piece of text or just a particular instance of it. Syntax SUBSTITUTE(Text, Find, Replace, Instance) Text (required) – This is the text you are going to search.Find (required) – This is the piece of text you want to find.Replace (required) – This is the piece of text you want to replace it with....

But finding and removing them from your data is actually quite easy in Excel. In this tutorial, we are going to look at 7 different methods to locate and remove duplicate values from your data. Video Tutorial What Is A Duplicate Value? Duplicate values happen when the same value or set of values appear in your data. For a given set of data you can define duplicates in many different ways....

It’s pretty common to want to know what day of the week a given date falls on and unless you’ve got some sort of gift for knowing that, you’re going to need a way to figure it out. In Excel, there are many different ways to determine this. In this post, we’re going to explore 7 ways to achieve this task. Format a Date as the Weekday Name The first option we’re going to look at involves formatting our date cells....

They’ll make certain things like navigating around our data much more difficult. But the good news is there are lots of ways to get rid of these unwanted rows and it can be pretty easy to do it. In this post, we’re going to take a look at 9 ways to remove blank rows from our Excel data. Delete Blank Rows Manually The first method is the manual way. Don’t worry, we’ll get to the easier methods after....

Example Generic Formula What It Does This formula will return a random selection from a given list with each item in the list having an equal probability of being selected. How It Works ROWS(List) will return the number of rows in the List. In our example there are 4 rows in the list. RANDBETWEEN(1,4) will return a random integer number between 1 and 4 where each number has an equal probability of being selected....