Excel and Google Sheets

Excel and Google Sheets Tips and Tricks

Text to Columns In Excel
This straightforward yet powerful feature in Excel allows you to format Text data to columns in excel swiftly. The goal of our technology as always to provide productivity in less time and effort. Rather than manually editing your data provided to you in text, this will show how easy it is to format your data.

Example: You received a receipt document copy in plain text format from your colleague. You need to copy this information to excel and add a column for license information.

 

  1. Copy your data directly from a text file or another excel document.

 

  1. In excel, select one Column> then go to Data tab>select Text to Columns.

  1. Under Convert Text to Columns Wizard…> click Next.

 

  1. Adjust necessary break lines> Click Next.

Note: In this example, we need to create a new column by separating the license name and numeric values next to it. Make necessary changes, like to add or rename column name.

  1. Click Finish.

  1. Data has been formatted>Add new Column name.

 


Auto Highlight Sheets

Highlight Duplicates 

    Google Drive:

    1. Select the whole column.
    2. Click Format.
    3. Click Conditional formatting.
    4. Click Add new rule.
    5. Set Format cells if to: Custom formula is:
    6. Set value to: =countif(A:A,A1)>1.
    7. Set the formatting style.
    8. Ensure the range applies to your column (e.g., A1:A100 ).

     

     

     

Highlight based on another cell

 

 

VLOOKUP

VLOOKUPS

Vlookup(Select Reference value, Select range to look into, Enter number of columns to look to the right, enter word "false")

 

i.e. =Vlookup(A2,C1:AE100,5, false)

 

 

Step by Step Instructions

 


Google Sheets ImportRange and Query

Importrange (google sheets only)

This function will pull in data from another spreadsheet and keep it up to date. It does not pull formatting, and you can't manipulate the copy of the data, but it’s a great way to share just part of a sheet with others.

 

=importrange("insert url","Sheet1!A:Z")

 

 

 

 

 

Query (google sheets only)

Guide

https://developers.google.com/chart/interactive/docs/querylanguage

 

This function pulls part of a data set

 

=query('Rereg Question Rep'!A:L,"SELECT * Where I contains 'Not' and L is NOT NULL and E<9")

 

=query('Rereg Question Rep'!A:L,"SELECT A,B,D,,E,L Where I contains 'Not' and L is NOT NULL and E<9")

 

From <https://docs.google.com/spreadsheets/d/1AO6NJyZBLJGVjNqK6XbuSXTKf4mJjid3p8daSuGm3pg/edit#gid=647783913>

 

If you use ImportRange combined with query, you have to use Column numbers instead of column letters. For example Col4 instead of D

 

 

 

 

ARRAYFORMULA({Importrange("sheetURL", "A:AJ4700");Importrange("sheetURL", "A4701:AJ9000")}).

 

From <https://support.cloud.google.com/u/0/chat/active?sessionToken=ACqlo9eHf3TbRahIAd_KLblDa7uUyaDCu4v4SgsDds1w7mOwcq5xv0h-MF9TUvygTq8FOEqxVL8PMps2BwjCzmasfBDX3prwUhMEbCsS8hxkJvqcnIQow09tTkhLJoWMw6CNiAmlCJktSABNryF8QH5QuN-Dr5YKRkWAOuOKGia4HZXfc54lkVRK3tQl5enYq3CWCJCX3HO6zXwRw_UHIrgWo-LF4sPn4brOp2N0jzQrs1e7UHEhkejQIZTKxaWU0LbjNIcgOlqLqA4HkfF7cNu8G_KHmFIvOkMPWQBBO_sPzQOhahM9Z5UmkITPBqnjVu4no8x91XYxCdULDvLPtDp62-vlx-aOv9EeqJrux4RzQQyXhRiJz91v&instance=1&clientTimeoutSec=40&hl=en>

Absolutely! Now, about the second inquiry, you can modify your formula and try to get all data (text) like this example > =Arrayformula(query({Format!A:R,To_Text(Format!S:S),Format!T:W}, "select Col13, Col12, Col15, Col17, Col18, Col19, Col20, Col2, Col1 where Col1 >= date '" &text(B3, "yyyy-mm-dd") &"' and Col1 <= date '" &text(B4, "yyyy-mm-dd") &"' order by Col1 ",1))

 

From <https://support.cloud.google.com/u/0/chat/active?sessionToken=ACqlo9eHf3TbRahIAd_KLblDa7uUyaDCu4v4SgsDds1w7mOwcq5xv0h-MF9TUvygTq8FOEqxVL8PMps2BwjCzmasfBDX3prwUhMEbCsS8hxkJvqcnIQow09tTkhLJoWMw6CNiAmlCJktSABNryF8QH5QuN-Dr5YKRkWAOuOKGia4HZXfc54lkVRK3tQl5enYq3CWCJCX3HO6zXwRw_UHIrgWo-LF4sPn4brOp2N0jzQrs1e7UHEhkejQIZTKxaWU0LbjNIcgOlqLqA4HkfF7cNu8G_KHmFIvOkMPWQBBO_sPzQOhahM9Z5UmkITPBqnjVu4no8x91XYxCdULDvLPtDp62-vlx-aOv9EeqJrux4RzQQyXhRiJz91v&instance=1&clientTimeoutSec=40&hl=en>

 

 

 

 

QUERY WITH DATE

 

AJ < date'"&TEXT(TODAY(),"yyyy-mm-dd")&"'

Query with reference to a cell

 

To make it work with both text and numbers:

Exact match:

=query(D:E,"select * where D like '"&C1&"'", 0)

Convert search string to lowercase:

=query(D:E,"select * where D like lower('"&C1&"')", 0)

Convert to lowercase and contain part of the search string:

=query(D:E,"select * where D like lower('%"&C1&"%')", 0)

 

From <https://stackoverflow.com/questions/23427421/query-syntax-using-cell-reference>

 

Export Filenames from Folder

You can simply paste the list into Excel, as follows:

1. Open Windows Explorer and select the source folder in the left pane.

2. Press Ctrl + A to select all items in the right pane.

3. Press and hold the Shift key, then right click on the selection.

4. From the context menu, choose “Copy as Path”.

5. Paste the list into Excel.


PowerQuery Index Column by SID

 

 

Add Custom Column

 

Table.AddIndexColumn([Count]. Call this new column "Sub Area No.", 1, 1)

 

From <https://www.myonlinetraininghub.com/numbering-grouped-data-power-query>