reducing used range in excel

Although Excel doesn't explicitly show this, there’s a Used Rangefor each spreadsheet. Used Range Won't Reset At All - Excel Help Forum To turn on manual formula calculation, in the Excel Ribbon go to Formulas > Calculation > Calculation Options > Manual. Figure 2. Represents the table new range. 9. Notice that in the above-referenced blog post, the selection method of updating a range was the slowest. Click on the row number below the last row we want to make it visible to others and press Ctrl+Shift+Down Arrow keys to select all the remaining rows of the worksheet. First, we’ll create the following dataset that shows the annual income of 26 different … Figure 2: Use the Visual Basic Editor to reset the used range of your worksheet. 2. Click on it. From the Select Data Source dialog box, select the data series you want to move. Add the Drop Down Lists. Sometimes the last cell of a worksheet (used range) may be well beyond the range of your actual used data (real range). In such a case, you can use one of the following tools to fix it: ASAP Utilities » Sheets » Reset Excel's last cell (Control+Alt+R) or the following tool which is more thorough and works on all selected sheets, but can be a bit slower. Check all formulas for references to entire rows. Sub vba_used_range () ActiveSheet.UsedRange.Clear End Sub Excel for the web offers access keys, keyboard shortcuts to navigate the ribbon. Another take on this issue: Do not bother about resetting the Used range, and just use the find method to search for the real last cell. If you have a table on active sheet with a header row, the code will first select the entire table then move down one row to exclude the header using Range.Offset method. Reduce Used Range In Excel. Reduce Excel file size… or change the tool. This tutorial provides a step-by-step example of how to create a histogram in Excel and how to modify the bin width so that the histogram looks exactly how you’d like.. The OFFSET function can be used within any other Excel function that accepts a cell / range reference in its arguments. From what you are saying just to confirm. Methods Used to Normalize & Standardize Data: Data normalization is generally being used in 2 ways: 1) In order to make a range of data easier to understand and assess: For instance; we have a list of math scores of 10 students. #3 right click on the selected rows and choose Hide from the popup menu list. For new & blank Excel files, the used range is only cell A1. your data is in range A1:J10, but pressing CTRL+END selects say M20 so I click on J10) Delete all the rows below the selected cell, save the workbook (CTRL+S) Delete all the columns to the right of the selected cell, savel the workbook. i.e. Re: Reset the used range. Use Names instead of Cell References When you create Named Ranges in Excel, you can use these names instead of the cell references. For example, you can use =SUM (SALES) instead of =SUM (C2:C11) for the above data set. Have a look at ṭhe formulas listed below. #2 press Shift + Ctrl + Down Arrow keys in your keyboard, to select all rows from row 5 to the bottom of the worksheet. The Excel VBA ReDim statement initializes and resizes a dynamic VBA Array. I've tried everything, and still the UsedRange of our 74-row worksheet insists on running until row 1048576 (in an XLSM workbook). Clear All on unused rows. This video requires a single set of data values and their associated time dates. 1. Enter or copy the following code into This Workbook code module Private Sub Workbook_Open () Worksheets (“Sheet1”) . I narrowed most of the weight down to one sheet, and did ctrl+End to check what is the last cell in that sheet. Microsoft Excel saves only the part of each worksheet that is in use, that is the section that contains data and/or formatting. You can create a named range in Excel for each data category, and then use that name instead of the cell references. Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. We can see in the above screenshot that the UsedRange is A1:D5. Minimize the used range To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. The chart is updated to display the new order … Step 4. This is the easiest and most efficient way of reducing your file size. Range refers to the cell(s) in Excel Worksheet. LEN function is a text function in excel that returns the length of a string/ text. In the COM Add-Ins box, check Inquire, and then click OK. Input x range – The range of dependent factors. This will hide all the rows below the used range. Move your cursor up one cell and then press the Delete key to clear cell TX5000. Fortunately, it is easy to translate the syntax in Excel VBA into Python. After we reset the used range, the filesize plummeted from 35MB to around 2MB. There’s no need to erase the Immediate Window – it will clear itself when you close Excel. You will now see the This Workbook code module- double click to open it 4. Select Data on the chart to change axis values. Using the MOD function will return … Screenshot from Microsoft Docs. The other options are discretionary and you may select them for your specific purpose. This issue may increase substantially the file size of your Excel workbook. If the used range includes millions of cells that aren’t even used, then the information that Excel saves regarding these cells can really blow out the file size. Imagine we have the data set in the screenshot below. Excel Named Ranges makes it easy to refer to data sets in Excel. Open the Developer tab in Excel, insert a Module, add the following macro, and run it Public Sub ResetUsedRange () ActiveSheet.UsedRange End Sub You could also tie this macro to an Excel keyboard shortcut for quicker access using the Application.OnKey event Share Improve this answer answered Oct 29 '13 at 22:33 Zack Graber 1,096 8 7 Add a comment Now press Alt/-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Please try the following methods. Hit ALT + F11 2. Do not worry about it, you can try Kutools for Excel's Set Scroll Area utility, which helps you hide all rows and columns except shared range with only one click in Excel. Using an excel camera tool is as easy as clicking a picture with a camera. This next optimization minimizes how frequently Excel has to respond to the selection changing in the workbook by minimizing the selection changing as much as possible. After we reset the used range, the filesize plummeted from 35MB to around 2MB. Click File > Options > Add-Ins. Click the Select Data button. Size of the used range. Press Ctrl+- on the keyboard to delete the blank columns. The improved functionality in Conditional Formatting in Excel 2007 makes this hack suitable for prior versions only . When you add rows or columns to a worksheet, formulas need to be changed or adapted to include the new information. You can limit the cells that suppress zero by applying a custom format. Yet deleting all the excess rows - it's too memory-intensive an action for Excel to handle. Normal.dotm is fine for now. On the Schedule sheet, select cells B2:B7, where the employee names will be entered. For example, if you try to use the formula =OFFSET(A1,3,1,1,3) on its own, it will throw a #VALUE! Press End-Home to move to the last used cell in your worksheet. Press Ctrl+A to select all the ranges in the list. I only really need a couple hundred rows. Usually this is caused by Excel thinking that the used range for one or more sheets is much larger than it should be. The point here to understand is minimum qualifying of an object by VBA processor. ASAP Utilities » Sheets » Delete unused empty ending rows/columns (on selected sheets) error, since a range to return (1 row, 3 columns) does not fit into a single cell. For new & blank Excel files, the used range is only cell A1. Before we start working to reduce the Excel this … To learn to create inventory management excel through a video, watch the video given below. The classic procedure to reset the used range in Excel is as follows: 1. If you compare it to the basic math formula for percentage, you will notice that Excel's percentage formula lacks the *100 part.When calculating a percent in Excel, you do not have to multiply the resulting fraction by 100 since Excel does this automatically when the Percentage format is applied to a cell.. And now, let's see how you can use the Excel … For example, =SUMIF(C3:C12,">70,000") would return the sum of values between cells C3 and C12 from only the cells that are greater than 70,000. Range Example: Again, see the Excel blog post quoted above. The Record Macro window will open. Let us call this range as B5:B16. Size of the used range; Size and placement of worksheet objects; Let’s look at each of these in detail. The Sln function can be used to calculate straight line depreciation in Excel during a single period of an asset's useful life. in the code. Formatting file from File properties. VBA Reference – Array Functions Array Choose Erase Filter IsArray Join Redim LBound UBound VBA ReDim Statement Syntax Parameters Preserve Optional. After doing all the tasks in excel it’s easier to compact … The picture below highlights the area of UsedRange Reset UsedRange UsedRange can be reset using the following Procedure. A histogram is a plot that can be used to quickly visualize the distribution of values in a dataset.. Press ALT+F11 to return to Excel 6. We can do it by following these steps. In Excel worksheet, we can press Excel shortcut key CTRL+END to select the last used cell. How about posting the macro that works for what you want, so we can figure out what you’re trying to do and determine if there are alternatives? (a... Enter the month names in a bunch of cells (Tip: Just enter the first month name and then click at the bottom right corner of that cell and drag to get all the other month names). 2. Click on ThisWorkBook in the Project VBA Window 3. There are two ways to reference cell(s) in VBA: Range Object – Range(“A2”).Value; Cells Object – Cells(2,1).Value; The Range object allows you to reference a cell using the standard “A1” notation. The range is a common term used in mathematics. The documentation contains the basic syntax in Excel VBA. I've deleteda number of rows (250) yet the worksheet still thinks the worksheet is it's original size (the scroll bar hasn't changed). We can reduce the steps for calculating the range by applying MAX and MIN functions in one line. Your cursor should now return to cell A10. This way the data size will reduce from 228KB to … The number of rows in the range is based on the count of numbers in cells B1:B6. On the Data tab, in the Data Tools group, click Data Validation. To change x axis values to “Store” we should follow several steps: Right-click on the graph and choose Select Data: Figure 2. Click OK – the Macro Recorder is now running. I have an Excel file that is larger than 7MB. Apply cell style Normal to unused rows. Saving the excel file from a .xlsx format to an excel binary format (.xlsb), reduces the size of your file by around 40%. My commented out code will only work for Excel 2010 and newer. Select the Edit button and in the Axis label range select the range in … Arup, The classic procedure to reset the used range in Excel is as follows: 1. Select the cell that you want to be the last cell (last row, last co... To select only the filled cells on a worksheet, you can use the Find dialog box. 2. Select the first empty cell after the used range and use the shortcut CTRL+SHIFT+DOWN key to select the entire column below the used range. However the code which I commented out does reset range to i7. For example, If you need to hide the rows and columns outside the range A1:C4, just do the following steps: #1 click the row 5 to select the entire row. LAMBDA functions are typically created and debugged in the formula bar on a worksheet, then moved into the name manager to assign a name that can be used anywhere in a workbook. Excel Formula Training. 1. It is actually a brilliant idea, search for "*" but set the order to xlPrevious. Full feature free trial 30-day, no credit card required! how do you want to do it if you do not use macros or VBA? It just gets updated automatically when we add new cells or rows. Press OK and we have finally made a … To set a Cell Value, use the Value property of the Range or Cells object. Select the chart. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4) Use vbNullString instead of ""(2 double quotes) : vbNullString is slightly faster than "", since … The depreciation of the asset over a specified number of periods can then easily be calculated by multiplying the calculated single … The window will have two selections: "Delimited" and "Fixed Width." You can no longer open as many Excel workbooks in the same instance as you could before you upg… In the Allow list, click Custom. and enter “UsedRange”. Get rid of unused cells by reducing the Used Range In old Excel files, some additional rows & columns might have been used a long time ago but the data has now been deleted. Thanks Most of the worksheets we work on are constantly being updated in terms of the data we need to store and manipulate. For more information, see Connect to a Custom SQL Query. This is called the used range. For example, if you try to use the formula =OFFSET(A1,3,1,1,3) on its own, it will throw a #VALUE! To delete an array formula, the entire data range will have to be selected and then you press the ‘Delete’ key. Rank can be used to differentiate the fast-moving inventory from the slow-moving inventory. Select the "Copy to Another Location" radio button to direct Excel to copy data rather than filter in place. 8. Click OK . ScrollArea = “B2:D10” End Sub 5. Keyword to be used if you want to […] Edit from the formula bar then press CTRL + SHIFT + ENTER. The range is a common term used in mathematics. Clearing the excess rows and columns to … The use of Intersect speeds up the macro by reducing the range checked to within the used range, making selection of entire columns feasible. First, you need to specify the worksheet. After clicking on Text to Columns window will pop up, called "Convert Text to Columns Wizard Step 1 of 3." Select the range A2:A20. A golden rule is to avoid having pictures in Excel. We do this with Excel VBA with the help of the For Each Collection loop.. As a result, you control which cells to suppress. Use the SUM function in Excel to sum a range of cells, an entire column or non-contiguous cells. The scores are ranging from 0 to 100, but we want them to range from 0 to 1 so as to assess it more easily. Type the number 100 in cell TX5000 and then press Enter. This short video details how to create a Timeseries Chart within Excel. The OFFSET function can be used within any other Excel function that accepts a cell / range reference in its arguments. Using the mouse to do all the task reduces your productivity. The LET function can also improve performance by reducing the number of calculations performed by a formula. To understand where the weight is coming from, I've used the method described in this SU question.. More information on use of Intersect and some other aspects of the above code can be seen in the notations for the Proper macro. The whole problem began when I used a macro to automatically fill thousands of rows of cells with formulas, so I'd never have to drag down formulas again. Click the Design tab. I don’t know. Actualy I got some restriction to use Vba macro by client. That’s why I am looking for some other solution. I am not sure is it possi... To overcome this problem, use Excel's MATCH function. Here we discuss how to find Range in Excel along … In the formula above, Column G is the value of n in each row. There are a million ways that are used to calculate the range of any dataset. After you upgrade to Microsoft Office 2013/2016/Office 365, you experience one or more of the following symptoms: 1. The OFFSET formula creates a range that starts in cell C1. For example, dates can be named ‘Date’, Sales Rep data can be named ‘SalesRep’ and sales data can be named ‘Sales’. How to Fix the “Used Range” in Excel To shrink the used range to the cells you’re using, simply delete the extra blank rows or columns: Select the first blank row (or column). Things I've tried: Delete unused rows. Excel Details: Excel has a “used range” for every sheet in your workbook.The larger this is, the bigger the file size becomes. The Range object contains Methods and Properties as shown in the figure below. MS Excel Shortcuts Keys, when starting with Microsoft Excel, knowing a few ms excel shortcuts keys will reduce your work time and make it easier to work on Excel. Press Ctrl+Shit+Right Arrow key. This will return the sum of the values within a desired range of cells that all meet one criterion. Make a note of the “Store macro in” option, as we will need to know where to find the recorded code later. You can easily do this via shortcuts by pressing Shift + Space for the current row, or Ctrl + Space for the current column. Click the "Data" tab, then select "Advanced Filter" from the "Filter" submenu to configure more advanced filtering options as well as to copy filtered data to a new spreadsheet. Excel’s used range property (UsedRange) includes every cell that’s ever been used. (Refer to Example 4 for your reference) Recommended Articles. Click on the cell you wish to have the last row and last column intersect on your sheet (e.g. By doing this, you’ll be reducing the size of your workbook and save on the amount of time it takes to run it out. LEN Function in Excel can be used to count the number of characters in a text string and able to count letters, numbers, special characters, non-printable characters, and all spaces from an excel cell. How to suppress a range in Excel. To set up such dynamic rolling months in Excel, just follow these simple steps: 1: Create a list of all the months. Write a Code with UsedRange Use the following code. Named ranges can make formulas easier to understand. If your Excel file has one table, click the sheet tab to start your analysis. Press Ctrl-Home to move your cursor back to cell A1. Use custom SQL to connect to a specific query rather than the entire data source. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. Resize Table Range To Exclude Header. Select the first empty cell after the used range and use the shortcut CTRL+SHIFT+DOWN key to select the entire column below the used range. Now hit CTRL+SHIFT+SPACE to select the entire row. Right now click on the selected rows. Find the hide option. Click on it. This will hide all the rows below the used range. Now no one can edit in those rows. Right now click on the selected rows. To reduce the file size, follow below given steps:- Open the worksheet. Find the last cell that contains data in the sheet. Delete all the rows and columns after this cell. In this example, to delete the rows, select the blank cell after the last cell D11 to the data. Select the cell D12. Press the key Shift+Space on your keyboard. This is exactly what had happened in the case of the spreadsheet concerned. Example to Resize Table by specifying static range. Select the cell that you want to be the last cell (last row, last column). Sometimes the last cell of a worksheet may be beyond the range of your actual used data. If the used range includes millions of cells that aren’t even used, then the information that Excel saves regarding these cells can really blow out the file size. Step 1: Create the Data. This allows us to loop through each cell inside the used range of the worksheet and, with the help of the IF statement, check whether we come across a formula cell and retain a count of the number of formulas detected. Only six numeric entries are in the list, so the range expands down only six rows. In Excel for the web, access keys all start with Alt+Windows logo key, then add a letter for the ribbon tab. set used … I want to "shorten" the worksheet to it's current size and lose the formatting for the unused cells. This is the range of cells containing any values, formulas, formatting, etc. Public Sub reset_usedrange () a = … Click – Developer -> Record Macro. In Excel VBA, the most common use of a For Loop is to read through a range. Oddly enough, that cell was in the last possible row in the sheet (i.e. Excel Details: The Ultimate Guide to Reducing File Size in Excel - Excel. For example, in Excel, a range—a rectangular area of the grid—is a first-class value. Sum Range. Hi – I think this might be what you are looking for. Try this series of actions on the sheet you want to change: > CTRL + End ‘puts you at bo... The function ROW( range ) returns the row number of the first row of the range, so ROW( A7:A99 ) returns 7. Then enter a dot (.) Our task is to write code that will read through the data and copy the amounts to the column J. We are only going to copy amounts that are greater than 200,000. The computer uses more memory when you open multiple Microsoft Excel 2013 workbooks, save Excel workbooks, or make calculations in Excel workbooks. Examples of Excel VBA UsedRange Property. In this video we count the number of cells that contain formulas on our worksheet. Range refers to the cell references below highlights the area of UsedRange reset UsedRange UsedRange can be in... Cells B1: B6 VBA Arrays and Dynamic VBA Arrays it refers to the cell that is. Hide all the task reduces your productivity use VBA macro by client ) Recommended.... '' https: //blog.hubspot.com/marketing/excel-formulas-keyboard-shortcuts '' > used range Ctrl+- on the worksheet reset UsedRange UsedRange can used. ) instead of the for each data category, and did ctrl+End to check what is last. The shortcut CTRL+SHIFT+DOWN key to clear cell TX5000, even though you erased it access toolbar down to sheet! Any dataset reference – Array Functions Array choose Erase filter IsArray Join Redim LBound UBound VBA Redim Statement syntax Preserve. Common use of a given dataset after that, that cell 3 ''... Live image there are a million ways that are greater than 200,000 ) Worksheets ( “ Sheet1 )... Double click to open it 4 data and copy the amounts to the last used cell that. //Www.Excelefficiency.Com/Fix-Slow-Excel-Files/ '' > Excel < /a > Excel < /a > we can do it by following these steps single... The keyboard to delete the rows below the used range is only A1... You used the method described in this SU question screenshot shown select Fixed Width. section select the entire (. X axis values though you erased it reset using the following code into this Workbook module! That returns the length of a given dataset > from Hide $ section! To Manage your inventory 2010 and newer row, 3 columns ) does not fit a... The keyboard to delete the blank columns is based on the Schedule sheet, and did ctrl+End to check is., the used range is a Text function in Excel has been a Guide to in! Caused by Excel thinking that the used range data tools group, data... From Hide $ Unhide section select the cell that contains data in the data need... Activate the worksheet in which you want to do all the ranges in the Manage box, check,! More memory when you just begin working with Microsoft Excel single set of data values and their associated time.. Thinking that the used range in Excel is as follows: 1 Excel - Excel Running Balance Excel... Can limit the cells that all meet one criterion cell of a given dataset some... Named ranges entries are in the cells group on Home tab, click data Validation feature, create! Some restriction to use the property or method that you would currently consider used ’.! The measurement between the maximum and minimum data of a given dataset delete Array... To overcome this problem, use Excel 's MATCH function Functions Array Erase! Currently consider used whenever the new information operations extend the used range: C11 ) reducing used range in excel the ribbon tab ranges. Other aspects of the for each Collection Loop //www.excelefficiency.com/fix-slow-excel-files/ '' > Excel < /a > Dynamic range in.! Information, see connect to a specific query rather than the entire data Source dialog box and... Workbook_Open ( ) Worksheets ( “ Sheet1 ” ) now return to cell.... Vertical scroll bars is normally determined by the dimensions of the spreadsheet concerned make calculations in Excel < /a how. Copy the following code into this Workbook code module Private Sub Workbook_Open ( ) Worksheets ( “ ”! Length of a for Loop is to avoid having pictures in Excel to handle ( )! 'S data Validation to prevent users from entering duplicate values Workbook that has recently increased drastically in size unused.... Can limit the cells group on Home tab, click on the.! Return ( 1 row, 3 columns ) does not fit into a single cell Fix a Slow Excel -! Rows - it 's too memory-intensive an action for Excel associated time dates do all the and... Ctrl + End ‘ puts you at bo now Running versions only translate. Ways that are used to calculate the range of any dataset to it too... This issue may increase substantially the file size, follow below given:. Bars is normally determined by the dimensions of the data tab, in the ribbon Hide... Current size and lose the formatting for the Proper macro each Collection Loop requires a single cell always whenever new. 'S MATCH function now Running a live image, I 've used the described! Its own, it will throw a # VALUE given below about increasing or decreasing cell number by percentage Excel! Button to direct Excel to copy amounts that are used to calculate the range expands down only six rows ribbon... Prev_Balance is referring to ) after clicking on Text to columns Wizard Step 1 of 3 ''.... < /a > 8 move to the last cell that contains data in the tab. Shown in the last cell ( s ) in Excel that returns the length of worksheet... Picture below highlights the reducing used range in excel of UsedRange reset UsedRange UsedRange can be to. Whenever the new set of lines are appended in the last cell ( row! When you add rows or columns to a worksheet may be beyond the range expands down only six.! Percentage in Excel D2: D21 improved functionality in Conditional formatting in Excel VBA Location '' radio to... Range in Excel for each Collection Loop you control which cells to improve performance the classic procedure to the... Beyond the range that you want to change: > CTRL + End ‘ puts you at bo also. Help of the used range column ) End-Home to move to the J... Access keys all start with Alt+Windows logo key, then add a letter for the that... However, you 'll use the formula bar then press CTRL Space ) 4 to avoid having pictures Excel... Most common use of Intersect and some other solution range is only cell A1 '' the worksheet property method! Efficient way of Reducing your file size in Excel VBA with the help the... Greater than 200,000 how I regret using that macro now copy to Another Location radio... To do all the task reduces your productivity, even though you erased it article talking... Start with Alt+Windows logo key, then add a letter for the web, access all. Be the last used cell to return ( 1 row, last column ) Excel, you control cells. You will now return to cell TX5000 worksheet, formulas, combine the SUM of the range! Dynamic VBA Arrays and Dynamic VBA Arrays and Dynamic VBA Arrays and Dynamic VBA Arrays powerful statistical tool, be... Automatically when we add new cells or rows can use =SUM ( C2: C11 ) for web! On quantities or cost price and accordingly plan your strategy to Manage your inventory 2007. Is because you used the count of numbers in cells B1: B6 efficient way of Reducing your file of! Us to use the formula bar then press End-Home to move to the RIGHT of that was. Only going to copy data rather than filter in place Excel does n't explicitly show this there. Following procedure blank cell after the used range list, so the of! Your strategy to Manage your inventory you 'll use Excel 's MATCH function even advanced users not. From Hide $ Unhide section select the blank cell after the used range significantly beyond range... Done in Excel a video, watch the video given below ( a Arup... Can create a named range in Excel < /a > how to use Visual... Columns Wizard Step 1 of 3. Efficiency < /a > 8 because you used the method described in example... My commented out code will only work for Excel - Excel should be names! Notice that unlike OFFSET, Excel highlights cell F14 ( the cell immediately to the cell. Most common use of a worksheet may be beyond the range of cells containing any values, formulas formatting. Vba window 3. include the new set of lines are appended in the case of the values a! Can do it by following these steps it 4 you press the delete to... Sure to check the difference between Fixed VBA Arrays: 1 trial 30-day, no credit card required is... Create named ranges with Alt+Windows logo key, then add a letter the. That unlike OFFSET, Excel highlights cell F14 ( the cell references Convert to! You erased it range for one or more sheets is much larger than it should be or copy the procedure! Cursor up one cell and then click OK – the range of your actual used data suppress! > select the chart your cursor will now see the Excel blog post quoted..... < /a > range in Excel < /a > select Fixed Width. order xlPrevious. Read through the data series you want to change: > CTRL + ‘! Edit from the formula above, column G is the easiest and most efficient of! To include the new set of lines are appended in the sheet you want to shorten. Working with Microsoft Excel RIGHT click on the data tools group, click data Validation,!? kb_id=83 '' > Excel < /a > the advantages of Excel Dynamic ranges enough, that cell.! Where the employee names will be entered always whenever the new information rank the stock on. Entire column below the used range significantly beyond the range of your used. May increase substantially the file size of your Excel Workbook we reset the used range one. Combine the SUM function with other Excel Functions column below the used for! To read through the data set in the ribbon to check what is the last used in!