Category Archives: Excel

Excel XLOOKUP vs VLOOKUP – Which is better and why?

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. With it came also other less known functions such as the XMATCH etc. This seems to end a decades old battle between fans of VLOOKUP vs INDEX MATCH. Even news sites caught this announcement as somewhat special. In this post we will understand the differences between both – at least based on the limited information we know.

When will XLOOKUP become available to Excel users? As per this article:

XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We’ll continue to optimize it over the next several months. When XLOOKUP is ready, we’ll release it to all Office Insiders, and Office 365 subscribers.

To understand the differences of XLOOKUP vs VLOOKUP it is best we compare the definitions of both formulas and explain using examples.

VLOOKUP Basics

Starting with a reminder of the VLOOKUP function. The Excel VLOOKUP function allows you to find a row in a column that matches a certain value and returns a value from another corresponding column in that row. It works like a phone book e.g. find the name of a person and return his/her phone number.
VLOOKUP Definition and Example
The problem with the VLOOKUP was that:

  • The lookup_value needed to be in the first column of the table_array. Otherwise you would need to reorganize the columns
  • The col_index_num was not a reference on the number of the column in the table_array where the returned value can be found. Hence adding a column in between would cause the whole formula to return values from the incorrect column
  • The range_lookup was most of the time a useless argument, usually being set to FALSE (exact match)

XLOOKUP Basics

The XLOOKUP does the same operation as a VLOOKUP, however, is much more flexible even in its most basic version (and familiar to an INDEX MATCH combo).
XLOOKUP Definition and Example
To summarize the benefits:

  • No longer do you need to worry about the lookup column being first in your table
  • You don’t have to worry about breaking any lookup formulas by adding or deleting columns from your table
  • You save time – as the basic version of XLOOKUP assumes you are looking for exact matches

Advanced XLOOKUP

We can also explore the full definition for the XLOOKUP function:

We already described the first 3 parameters however we also have 2 remaining:

  • match_mode – the type of match you want to make. What is interesting is the mode 2 allowing you to use ? and * wildcards to replace a single or any number of characters in a lookup operation xlookup match mode
  • search_mode – type and direction of search. Useful when there is a particular sorting order of data.xlookup search mode

I can’t wait to try this function out on actual data.

XLOOKUP vs VLOOKUP – Conclusions

Although XLOOKUP is still only recently announced I already see it as a final resolution to the problem of lookup operations in Excel. I am frankly much surprised it took Microsoft so long to introduce this function. If you are interested in how XLOOKUP will compare in performance to VLOOKUP stay tuned.

Why is my Excel file so LARGE? Learn how to reduce Excel file size!

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

In this post I will explain various ways to reduce Excel file size. Large Excel files slow down your system, increase the likelihood of the file crashing as well as obviously use more drive space.XLSB files save or open faster

Microsoft Excel was designed to be a tool for managing relatively small datasets for Finance & Accounting purposes, today we see that Excel is used often for large data analysis, prototyping of complex solutions (built often with VBA Marcos). You probably wondered here struggling with bloated Excel file, frustrated with having to wait for calculations to complete or constantly crashing Excel file.

Reduce Excel file size… or change the tool

Before we start working to reduce the Excel this is a good moment to ask yourself the question…

Is Excel the right tool for the job?

If you are convinced so proceed further, otherwise consider that Microsoft Excel has built several tools dedicated to managing with challenges that appear when working with larger data sets:

  • PowerQuery – this Microsoft AddIn for Excel was created specifically for pulling and running queries on large datasets in Excel. You can easily merge worksheets, csv files or run complex relationship queries across your worksheets
  • PowerBI – Microsoft PowerBI is a free application you can download from the Microsoft Store that allows you to create custom dashboards, run complex analytics… and easily publish your dashboards and reports online

Save file as Binary (XLSB)

xlsb vs xlsx
Saving a file in XLSB from my experience often resulted in reducing file size even by 70%.. If you don’t know the difference between a XLSX (OpenXML format) and XLSX (Binary Excel format) file format I encourage you read my post on XLSB.

Since Excel 2007 Microsoft was pressured to open up the Excel format so other applications could use it. This meant moving away from the binary file format which was storage friendly and would open efficiently, to the more heavy file format (XMLs files compressed to a ZIP file). In effect the XSLX file format will always be larger than the XLSB.

To save a file in binary format go to FILE click on Save As and select Excel Binary Workbook (*.xlsb) as shown below.
Save Excel file as XLSB

Remove Used Cell Range

A troublesome thing that can increase your Excel file size is Used Range. Imagine the worksheet below. Let us assume at some point I added some data into cell G16, and afterwards I deleted it. Although de facto I am only now using A1:C4, de jure Excel will keep in memory the entire range of A1:G12 expanding the file size.
Unempty Cells vs Actually Excel Used Range

How to check your Excel Used Range? Use the CTRL+END key combo. You will be moved to the last cell in your Worksheets Used Range

Removing Unused Ranged

So what to do to reduce your Used Range only to the actual range you are using? Follow the steps below:

Remove data and formatting from unused cells

Select all unneccesary cells that contain data or formatting and go to Home->Editing->Clear and select Clear All:
Excel Delete Unused Range

Reset Worksheet Used Range

Now we need to update the Used Range property in your Worksheet. Go to the Developer Tab and open the VBE. Next add the following VBA Macro to any VBA Module.

Next run the Macro (or use the F5 shortcut). The Used Range should be now updated.

Remove Hidden / Unused Worksheets

Another reason for large file size is having many unused or hidden worksheets in your Workbook. Each Excel Worksheet has it’s own share of metadata, more importantly, however, you may be keeping sheets with similar datasets, copies or unnecessary Pivot Tables that also take up a lot of space. Follow steps below to show and delete unnecessary hidden worksheets.

Unhide hidden worksheets

To unhide hidden worksheets right-click on a Worksheet and select Unhide:Excel Unhide Hidden Worksheets

Delete unused Worksheets

To delete an unused Worksheet right-click and select Delete:
Excel Delete Worksheet

Remove Formatting

Formatting adds additional kilobytes to your Excel file size. A good approach is to remove any formatting from cells that don’t need formatting.

To remove cell formatting you can read this MSDN article or follow below:

Select Cells and click Clear Formats

Select Cells for which you want to remove formats. Look for the Editing section in the Home ribbon and select Clear Formats:
Excel Clear Formatting

Compress Images

Another reason for Excel to have an unreasonable file size is due to media, especially Images. Although you may think cropping and Image and resizing reduce its size, in fact Excel still keeps the entire image in memory. Hence the only way to free Image memory is to Compress Pictures.

Crop and Compress Images in Excel

To Compress your Images click on your Image(s) in Excel and go to Format->Adjust->Compress Pictures. Next select your preffered options. Select Delete cropped Areas of pictures to remove the unseen cropped areas of your images as well as the preffered resolution (the lower the more pixelated the image will seem):
Excel Crop Images and Reduce Resolution

Other options

Other honorable mentions to reduce your Excel file size may be:

  1. Remove unused Pivot Tables
  2. Remove Pivot Cache
  3. Replace Formulas with Data (Copy Paste as Data)

Feel free to comment below and add your ideas!

VBA Timer: Create a Stopwatch in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

In this post we will explore the various ways we can set a Timer in Excel VBA as well as how to build your own VBA Stopwatch. Setting timers can be extremly useful if you want to run code as specific time intervals. On the otherhand a practical example can be a VBA Stopwatch which can be useful to measure time elapsed directly in a Excel spreadsheet.

VBA Timer

The most simple example of a VBA Timer can be made using the VBA Timer function:

VBA Timer with Hours, Minutes and Seconds

The above is very useful if you want to measure time elapsed in Seconds, if you want to calculate time elapsed in Hours, Minutes and Seconds you can simply use the VBA Now function with text formatting using VBA Format instead:

VBA Wait

In case you don’t want to measure time elapsed and instead set an interval or delay between code execution you either use the VBA Sleep or VBA Wait procedures like below. Remember that the VBA Sleep function is not a native VBA function and needs to be declared.

VBA Alarms and Scheduling

Another VBA Timer scenario is scheduling procedures to run at a specific time or after a specific time interval like an alarm clock. To do this we use the Excel VBA OnTime procedure:

You can use the VBA OnTime function also to schedule code execution after a specific duration of time. The below will save the current file after every 5 min.

VBA Stopwatch

Using the VBA OnTime function we can also create a simple Excel VBA Stopwatch:
VBA Stopwatch in Excel
As you can see in the example above I created a button that launches a Start / Stop sequence. When the stopwatch is running it increments the TIMESTAMP cell (a named cell). You can use the code below to achieve this. Remember to select your named range as well as to connect your button to the StartStop procedure.

What happens above? When you hit the button the AddSecond procedure will be set to run within a second and then automatically sets itself to run in the next second after updating the timestamp. The next hit of the button will turn of the schedule. This approach may see some slight delays over a longer duration of time (as we are running code before the schedule), however, it should be good enough.

Dynamic Charts in PowerPoint – Self-refreshing Charts using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Technically in PowerPoint you can link an Excel file to a PowerPoint presentation. However that will not make the Chart dynamic, and sometimes it is hard to say when the Chart will be updated. But what if we want even more – what if we want the chart to refresh during a Presentation? I can imagine this can have many uses. Imagine wanting to present a series of dynamic slides in an Office environment, each slide showing latest charts from an updates Excel dashboard. In other cases you may want to prevent any linking out to Excel files and simply have a dynamic reference to your source file or files. Today we will explore how to create Dynamic Charts in PowerPoint using VBA.

See an example in this Youtube video:

Why not use Linked Charts?

Before we start I want to explain that you don’t need to use the approach explained below to simply link an Excel Chart to a PowerPoint presentation. If this is all you need, read this Support Office article. The purpose of this exercise is to avoid linking at all and make it entire flexible on your own preference when you want to update your Charts and even update the Excel files before doing so.

Creating Dynamic Charts in PowerPoint

Below is a step by step tutorial of how to setup the Charts and Macro:

Create the PowerPoint and Shapes

First we need to create a PowerPoint Shape that will be used as a placeholder to mark the location and size of our Chart. Click on image below for a reference.

PowerPoint with Placeholders for Shapes
Click to enlarge

Create an Excel Chart

Next we need to create and name an Excel Chart. This will be copy pasted as an Image to our PowerPoint to avoid linking and to assure the format is identical to how it is visualized in your Excel file.

Excel file with Chart to be copied to PowerPoint
Click to enlarge

Create the Dynamic Charts VBA macro

If you don’t know how to access your Developer tab you can proceed similarly as in Excel. Next create a new VBA Module and copy the following VBA code:

Let me break this down. Above first we defined the VBA Sleep function as we will use it as a delay mechanizm between updates. Next we define the CopyChartFromExcelToPPT function which basically does the following:

  • Open an Excel file defined with excelFilePath
  • Copies the chart chartName from the sheet sheetName
  • Pastes it into the ActivePresentation on slide dstSlide an the location shapeTop and shapeLeft and with the size of shapeWidth and shapeHeight

As you can see this is a generic function you can reuse to your own purpose regardless of the example usage below.

Auto refresh the Chart

Now let us create simple scenario using the generic function for copying an Excel Chart to PowerPoint:

  1. Run in Presentation mode
  2. Every second update the Excel Chart and update the TimeStamp
  3. Exit Presentation mode

Below VBA code does exactly that:

Use Cases of Dynamic PowerPoint Charts

For me a way to animate Excel Charts in PowerPoint present a new pallete of options to further push the boundries of what we can do with PowerPoint. Below a short list of ideas that can help you image how useful this can be:

  • Create a dynamic Office presentation with auto refreshing Charts from multiple Excel files
  • Create a button to manually refresh single or all Charts in your PowerPoint deck
  • Create an easy way to link your charts in PowerPoint to Excel files that might move (e.g. link to Excel files in same directory

Have more ideas? Raise a Question or let us meet on Reddit.

VBA Paste from Excel to PowerPoint

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

In this post we will explore how VBA paste from Excel to PowerPoint objects such as a Range, Chart or other element. Below you will find working code snippets. We will learn also to modify this routine to address different VBA Copy Paste from Excel to PowerPoint.

VBA Paste Range from Excel to PowerPoint

We will start with an example of VBA Paste Excel Range into PowerPoint as Picture as this is the most typical scenario. For this I created a custom function called CopyFromExcelToPPT:

What does the VBA Function do? In short you need to provide the following parameters:

  • excelFilePath – full file path to the Excel from which you want to copy a VBA Range
  • sheetName – the Sheet name from which you want to copy
  • rngCopy – the VBA Range you want to copy
  • dstSlide – the number of the slide (starting at 1) to which you want to copy the Range
  • shapeTop Optional. The Top position in pixels of the new pasted Shape
  • shapeLeftOptional. The Left position in pixels of the new pasted Shape

Let us use this function in the following scenario. We want to copy range A1:B4.
VBA Paste Excel Range to PowerPoint as Picture
Let us use our function above for this scenario

VBA Paste Chart from Excel to PowerPoint

Now an example of VBA Paste Excel Graph into PowerPoint as Picture as this is also a useful case. For this I created a custom function called CopyChartFromExcelToPPT:

Again let us use it on the example below where we want to copy a Chart from a Excel Workbook to PowerPoint:
VBA Paste Excel Chart to PowerPoint as Picture
Example execution of the VBA Function below:

If you want to place the Chart at a specific place use the shapeTop and shapeLeft arguments. The below will place the chart at 10 pixels from the Top and 100 pixels from the Left.

Changing Height / Width of pasted elements

In the examples above we didn’t change the Width and Height of the pasted Range or Chart. To do this use the adjusted functions below: