Here are 7 great keyboard shortcuts to quickly perform file menu commands.

Excel allows a variety of date formats and depending where you’re from the default formats will be different. A date like 31st December 2016 might appear formatted in Excel in any of these ways: Dec, 31 20162016-12-3131-12-201612/31/2016 (mm dd yyyy format is only used in the US)31/12/201616-Dec-31 There are many more possible formats and you can even create your own with custom formats. In this example of Dec, 31 2016, the date is really stored in Excel as the value 42735 regardless of the format....

Step 1: Replace Any Formula With Text Replace any formula with text in the range which you want to transpose. Step 2: Copy And Paste Transpose Now we can transpose our selection with copy and paste special. Step 3: Change The Transpose Range Back To Formula Now we just need to change the text in the transposed version back to formulas. Now we have a transposed version of the original range and all the formula’s have maintained their original references....

The formula then uses the MATCH function to find the position in the array of the first TRUE value. This position is used by the INDEX function to return the value in ResultsRange from the same position. In our example, EXACT({“Aaa”;”aaa”;”Bbb”;”bbb”;”Ccc”;”ccc”},”aaa”) results in the following array of Boolean values. This results in one TRUE value in the second position where we have an exact match to “aaa“. MATCH(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0) will then return 2 as a result because the first TRUE value is in the second position....

At some point in your career as an Excel user, you will need to delete a sheet from your workbook. Maybe it’s no longer needed, or maybe you made a mistake and need to start over. In any case, deleting a sheet is a fairly straightforward process that can be accomplished in just a few simple steps. This post will show you how to delete a sheet in Excel using several different methods....

Saving A New Workbook The First Time If you’ve created a new workbook and haven’t saved it yet, then you will notice at the top of the workbook Excel has named the file for you with its generic naming Book1, Book2 etc… When saving a workbook for the first time you will need to use the Save As command to give the workbook a name and location to save it....

Excel allows users to create multiple sheets in their workbook solutions. But you might only want the user to see a selection of those sheets. Excel lets you hide any sheets you don’t need. This is a great way to organize your work and make sure that only the essential information is visible. This can help to declutter your workbook and focus the user’s attention on the information they need to see and make it easier to navigate your workbook....

Microsoft Excel is a powerful spreadsheet application that offers users a variety of features and capabilities. One of the most popular features of Excel is the ability to create and run VBA macros. A macro is a small scripts written in the VBA (Visual Basic for Applications) programming language that can be run in your desktop Excel app. Macros can save you a lot of time and energy when working in Excel....

These are keyboard shortcuts for moving the active cell around a selected range of cells. Enter – Move the active cell one cell in a direction based on your settings. This is down by default.Shift + Enter – Moves the active cell up to the previous cell in the range.Tab – Moves the active cell to the right in the range.Shift + Tab – Moves the active cell to the left in the range....

Pivot tables are one of Excel’s most powerful tools. They allow you to quickly turn thousands of rows of data into meaningful summaries with an easy to use drag and drop interface. In the example we’re going to go through, we will look at sales data with 5,000 records. Each record or row of data pertains to one sale to one customer and contains the follow information. Sales Representative – This is the person who made the sale....

Syntax ISTEXT(Value) Value (required) – This is the value you want to test if it’s text. Example In this example we test a few values. Notice that even though a value can contain numbers the result is still text.

Syntax MONTH(Date) Date (required) – This is the date value that you want to return the month from. Example In this example we use the function on several dates in different formats. Notice the text values we try result in a #VALUE! error while numerical values outside of the range 1 to 2,958,465 result in a #NUM! error.

Syntax ROW(Range) Range (optional) – This is a single cell or range which you want the row number from. For ranges the row number of the upper left most cell will be returned. If no range is selected, the row in which the formula appears is returned. Example In this example we get the row number based on a single cell reference, no cell reference and a reference to a range of cells....

Move over VLOOKUP, your time in the spotlight is over! XLOOKUP is here and it can do everything VLOOKUP can do and more! Let’s take a look at this new function in detail. Video Tutorial Problems with VLOOKUP Why bother introducing a new lookup function? VLOOKUP works just fine, right? Well, there are a lot of problems with VLOOKUP that a beginner user might not be aware of. When we’re not aware, things can go wrong and we might end up getting incorrect results....

Syntax FALSE() There are no arguments for this function. Example In this example we return the FALSE value.

Syntax PROPER(Text) Text (required) – This is the text string which you want to capitalize the first letter of each word. Example In this example we capitalize the first letter of each word.

How It Works We have a table of data containing a list of student names along with the university they attended, the subject they majored in and their grade average. If I asked you to tell me “what was the major of Reed Gray?” based on the above table of student data, you would likely tell me it was Computer Science. You would be correct, but how did you find this answer?...

This post is going to show you all the time-saving keyboard shortcuts you can use when working with rows and columns in Excel. Rows and columns are the building blocks for every Microsoft Excel worksheet. They provide a structure for your data and make it easy to enter, sort, and manipulate information. Keyboard shortcuts can save you a lot of time when working with rows and columns in Excel. They help you select, insert, delete, hide, unhide, or resize your rows or columns....

A well organized spreadsheet can make a big difference for someone looking at it for the first time. This can lead to less time required to comprehend and use the file properly with fewer errors. Naming sheets appropriately is an integral part of a properly organized spreadsheet. In this post, I’ll show you 7 ways you can use to rename the sheets in your workbooks. Video Tutorial Rules for Sheet Names There are limits to what you can name a sheet....

You’ll probably quickly notice Excel will automatically remove the leading zeros from any numbers. This can be really annoying if you want those leading zeros in your data and you don’t know how to make Excel keep them. Fortunately there are quite a few ways to pad your numbers with zeros at the start. In this post, I’ll show you 9 ways to add or keep those leading zeros to your numbers....