Blog

What is an Excel Pivot Table

Posted on by DevPaulC
Pivot tables are Excel's most powerful feature. A pivot table lets you summarize detailed data automatically. In this tutorial you will learn what is an Excel Pivot table and how to create an Excel pivot table to summarize a simple dataset. Case Study A grocery store wants to keep track of daily sales of the main fruits thei are selling: Apples and Oranges. They start with a blank Excel sheet and each day add two lines. One for the sales of Apples and one for the sales of Oranges. Towards the end of the month, the store manager wants...read more

Today’s date and time in Excel

Posted on by DevPaulC
Use Excel to calculate today's date and today's date and time with the functions TODAY() and NOW() plus variations and keyboard shortcuts. These guides work for Google Sheets as well. Today's Date To simply add today's date, use the function TODAY() with no arguments Add or subtract integer numbers to the TODAY() function to get tomorrow's date or yesterday's date add a 7 and you get next week's date and so on Today's Date and Time To calculate today's Date and Time, use the function NOW(). This will add the current time to the cell. same as with TODAY()'s...read more

The excel ribbon

Posted on by DevPaulC
When first introduced, the ribbon was not liked by many experienced Excel users. Personally I hated the ribbon. Now, I can't even imagine how Excel looked before the ribbon. If you don't know, or don't remember, this used to be instead of the ribbon. How does it look to you now? Compared with the current ribbon Much better, right? The ribbon is not just one ribbon. It is actually 10 ribbons that change depending on the tab selected. Each tab has it's own set of commands. The Home tab has worksheet related commands, the Insert tab has commands related...read more

Simple Worksheets Commands

Posted on by DevPaulC
As seen in the previous section "What is a worksheet" once we have a brand new worksheet, we can do some commands in regards to that worksheet. To access a list of the quick commands, right click on the name of the worksheet. List of worksheet commands How to insert a new Worksheet and more The Insert action is used to insert a new worksheet. Similar with the + sign, if you press Insert, you get the option to insert a black worksheet. While the + sign immediately inserts a new worksheet (the most common thing to do), with...read more

How to use the CONCATENATE function in Excel

Posted on by DevPaulC
If you want to join multiple texts, numbers or function values into one cell, Excel has a function called CONCATENATE(). You can use the function is some very creative ways. Simple text concatenation The CONCATENATE function works like this: = CONCATENATE( text1, text2, text3). In the example below, text1 in in A1 cell, text2 is a space (if you specify text you have to put it in quotes) and text3 is in cell B1. Addind Good, a space and Morning will result in "Good Morning" Concatenate text with numbers Concatenate works with text and numbers just as well. The...read more

How to use XLOOKUP in Google Sheets

Posted on by DevPaulC
XLOOKUP may be a new function to Excel but it is not new to spreadsheet software. Google Sheets already has the LOOKUP function that works exactly like Excel's XLOOKUP. What about Excel's XLOOKUP support for Google Sheets? As Excel Released the XLOOKUP for most of the users now (if you don't have XLOOKUP in your Excel see here why), many users are asking what happens if you try to open a file that has an XLOOKUP formula in Google Sheets. As of February of 2020, this is not supported yet. You get an error when you try to do...read more

Multi-row and Multi-column Pivot Table

Posted on by DevPaulC
In the previous tutorials for Pivot Tables we looked at the simple Pivot tables with one row and one column, then we added a category to the data-set to create a multi-row pivot table. In this tutorial we will use one of the existing columns in our data set, the column "Date" to build on the pivot table we created in the previous step and add columns. Note: If you are a Google Sheets user, Pivot Tables in Google Sheets work very similar. Initial Data Set and Pivot Table This is a simple Pivot Table with one Value for...read more

Multi-Level Row Pivot Table

Posted on by DevPaulC
If in the previous example we studied how to create a simple pivot table from a simple data-set, in this example we add an additional column to categorize our products in order to create a Multi-Level Row Pivot Table Case Study A grocery store wants to keep track of daily sales of the main fruits thei are selling: Apples and Oranges. They start with a blank Excel sheet and each day add two lines. One for the sales of Apples and one for the sales of Oranges. Towards the end of the month, the store manager wants to summarize...read more

How to use SUMIF in Excel

Posted on by DevPaulC
SUMIF formula is great for summarizing data in a large dataset. A better method to summarize the data is a pivot table. But if only a quick summary is required, SUMIF might be an easier method. Here is the dataset In a previous tutorial you can see how to do a pivot table of this 1464 row dataset and sum up all the sales by category and products. The SUMIF method Another way to obtain the same result as in the pivot table is to use a SUMIF function. For example, the SUMIF could summarize the sales for Broccoli...read more

How to update Excel 365

Posted on by DevPaulC
If you think you may not be running the latest version of Excel as part of the Office 365 subscription, follow he steps below to check and update your desktop version. Note: If you are looking for an updated version of Excel to try the new XLOOKUP, you may not have it yet if you are on a Semi-Annual Channel. Learn about the Office update cycles and how you can still use XLOOKUP even if it's not in your desktop version of Excel. 1. Go to the file menu in Excel. Click on account 2. Click on the drop-down...read more