Skip to main content


Showing posts with the label Excel

Excel filter option cannot be found

Working in a large data set in Excel but filter option cannot be seen or found even clicking the sort and filter option multiple times, the drop-down filtering options is nowhere to be found.
Well, just get back to the very basic steps. If it can’t be seen then it might be hidden, right?
So, when working in a large data set but some other rows is not needed and need to be hidden. Just leave the first row visible and do not hide it, then hide other rows.
The drop down filter option is in row 1, so if it is hidden then of course the drop down filter will also go with it.
To unhide row 1 or cell a1, press Ctrl + G. The go to options window will pop-up, type: A1 to go to cell A1. (see pic below)

Then, press ctrl+shift+9 to unhide the row. Or click on the format menu options and select unhide. (see pic below for excel 2016). Then the filter option will be displayed.

The keyboard shortcut I think will work on other versions of Excel.
If you need the filter option in Excel, row 1 must never be hid…

Excel footer or header location

How to insert a header or footer in Excel 2016?
How to insert a custom header or footer in Excel 2016?
To insert a custom header or footer with a picture or other sort of customization, can only be done of course using the custom header or footer option.
The easiest way to find header and footer is clicking on Insert on the main menu and selecting or clicking header and footer options on the ribbon tab as shown in the image below.

If the author of the file has inserted a custom footer or header, sometimes the footer it’s either the header or footer will not be shown.
If you need to delete, remove, change or alter the custom footer or custom header, it can be done by the steps below. Steps is shown in the image below. Click on “File”, click “Print”, click “Page setup” a small text found on the bottom right side of the print window. After clicking “Page Setup”, a window will open, then click on “Header/Footer” option.

Cheers, till next time! Hope it helps to make life easier.


Excel not enough free memory

Opening files in Outlook shows an error message that there is not enough free memory to run the program.
But the computer has enough RAM installed and no other application is running on the computer except Outlook.
Office programs by default don't trust or block files originating from the Internet.
This is of course a good security settings but if this setting is getting to your nerves because even if the document that is coming from your own domain you are not able to open.
Or the file is originating from your own personal email account other than your company email but when it goes to your Outlook you cannot open it. This is the scenario where good and harsh words will start crossing your mind. You will need to ask what the heck is going on?

The Microsoft link below provide some solution on how this thing can be resolved.
The article title of the link below is:

Error message in Outlook: "There is not enough free memory to run this program. Quit one or more programs, and th…

Set a graph or shape in Word behind text using VBA

How to set a graph or shape in Word so it stays behind the text using VBA?
In Word the option is to right click an object select wrap text and click the desired options such as behind text, in front of text and other options.
Setting a graph behind the text is quite useful, if you need to display some text that will be displayed together with the graph.
To do this via VBA is plain and simple, a one liner code.
ThisDocument.Shapes("NameOfTheChart").WrapFormat.Type = wdWrapBehind
wdWrapBehind is equal to set "Behind Text".
The one liner VBA code, of course it will apply to any shapes, circle, oval, square or other shapes not just a graph.
Code works on Word 2016, it may or may not word in previous version of  Word. 

Cheers. Till next time!

================================ Free Android Apps:
Click on links below to find out more:
Catholic Rosary Guide  for Android:…

How to rename a chart in Word 2016

Do you want to check what’s the name of the chart in Word 2016?
Or you just want to rename the chart in Word 2016 to a name, that the chart represents?
Word 2016 default chart name is in sequence, like Chart1, Chart2, Chart3 and it follows this pattern depending on how may charts you have at your document.

If you have multiple charts in a word document, and you want the chart name it to something meaningful or what the chart itself is all about; it’s quite easy and straight forward to rename a chart, but the way to rename it is not conventional as the previous way of doing it in other version of office.
You can rename the chart via VBA code or using the GUI in Word itself.
To rename or check the name of the chart in Word 2016 or O365.
Click on the chart:

After clicking the chart, the "Chart Tools" will open at the Ribbon.
Click the "Chart Tools"  at the ribbon. Click on the "Format" tab.
Under Format tab, click on "Selection Pane", this will op…

Sort numbers, digits or figures in Excel 2016

To sort numbers, digits, or figures in Excel 2016 is quite straight forward.
There are two ways to do it via the Home tab or Data tab.
What is found on the Home tab is sorting of alphabets or A-Z option which can easily be seen beside the auto-sum function which is on the right most tab of the ribbon by default.
Screen shot below shows the sorting functionality found in Home tab.

The other way to sort by numbers, digits or figures in Excel 2016 is to click on “Data” click on “Sort” then an option will appear to sort by smallest to largest or largest to smallest.
A screen shot below shows where to find the function.

That’s it hopes it helps to make thing easier.

================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide
Linux Android App cheat sheet: Multiplication Table for early learner…

Excel VBA set worksheet visibility

Set excel worksheet visibility by changing its property.
Simple and a one liner code to hide and unhide worksheet in Excel.
Sub Visible_Sheet()
Worksheets(1).Visible = 1  'Make the worksheet visible
End Sub
Sub Hide_RightClick_Unhide()
Worksheets(1).Visible = 0    'Hide Worksheet to Unhide Right click on Sheet name and unhide the worksheet
End Sub
Sub Super_Hide()
Worksheets("calcx").Visible = 2
 'Hide Worksheet but right click on Sheet name will not show whether the sheet is hidden or not
End Sub

Cheers! Till next time.

================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide
Linux Android App cheat sheet:
Multiplication Table for early learners
Catholic Rosary Guide  for Android:…

Excel VBA copy or reference a value

Excel VBA provide options on how to pass value from worksheet to worksheet or from workbook to another workbook.
To optimize or make the VBA code run faster is to make the worksheet interaction lesser.
Recording an Excel VBA macro to copy and paste a value will have a similar output to the code below.
Sub Macro1() ' ' Macro1 Macro '     Range("A2").Select     Selection.Copy
    Sheets("Sheet2").Select     Range("A3").Select     ActiveSheet.Paste
End Sub
what the code does is to select the cell on the active sheet for copying and select another worksheet and select the cell where the value will be placed and paste the copied cell value.
Steps are: a. Select the cell and copy the value b. Select the worksheet where the value is to be copied c. Next step, is select the cell on the selected worksheet d. Paste the value on the cell
Steps are absolutely fine, if it works as expected. But if you are copying hundreds or thousands of cells. It will be a w…

Excel find last row with value in a specific column

1. How to find last row with value using vba in a specific column?

2. How to find last row with value  using vba in a worksheet?

The questions above are entirely two different scenarios.

The first question will find the last row with value in a specific column within the worksheet.

While the second question find the last row with value of the entire worksheet.

The first question is applicable if you need to find the last row in different columns.

Let's say Column 1 or Column A, or Column 2 or Column B etc.

Example code below will find the last row in a specific column via VBA code.

'command Cells(Rows.Count, 1) = find the last row with value in Column 1 for Sheet3

Dim last_row As Integer

last_row = Worksheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row

Msgbox last_row

'replace 1 with the desired column 
'ex: command Cells(Rows.Count, 7) = find the last row with value in Column 7

If you don't need to specify a worksheet or…

Outlook does not display picture

Have you received an email saying please find the picture below, but the picture is on the attachment not on the body of email.

Or sending out a nice an beautiful layout of graphics hoping to impress a colleague or client only to find out that the the graphics doesn't display correctly in Outlook or either in the mobile phone.

HTML Tables has been a big help for web designers or developers before the CSS era or other good platforms at this time.

Well, if picture is not displaying properly in Outlook or mobile phone. Embedding or inserting the picture in a Table, seems to be a good quick solution in inserting pictures via Outlook and it will display properly in mobile phone also.

When composing email in Outlook, click on the Insert option and click on insert table. If sending two pictures, you can insert a two rows or two columns and insert the picture on each row or column.

After sending out the email, check the email in Outlook or mobile phone and if everything goes okay the pic…

Create a progress bar in Excel

Create a progress bar in Excel that varies on the input value and range.

If the value is  equivalent to 100% or maximum input is reach then the color will fill the whole cell.

This example below was created using Excel 2010, the logic should be the same with other version that supports this function.

1. Select the cell, that will have the progress bar.
    Click on "Home" tab, click on "Conditional Formatting"
    - In drop down menu select Data Bars
       - In the sub menu click "More Rules".

See screen shot below:

2. After clicking "More Rules", "New Formatting Rules" window will open.
    - In "Select a Rule Type"
       "Format all cells based on their values" should be selected
    - Under Rule Description
       Set the type to "number"
       Set the range of minimum and maximum value
       Select the color that you want and click "OK", once customization is done.

See screen shot below: