u/Good-Willingness2234

Hello everyone, I wanted to know if there is any reliable free VPN software, I use the Norton 360 Deluxe ones but they are slow.

I've compiled the 30 most useful shortcuts for accounting and daily data management. All tested on Windows.

File & Workbook Management

1. Ctrl + N: New Workbook (Creates a new file)
2. Ctrl + S: Save Workbook (Saves current progress)
3. Ctrl + W: Close Workbook (Closes the active file)
4. Ctrl + O: Open Workbook (Opens an existing file)

Data Entry & Editing

1. F2: Edit Cell (Enters the active cell for editing)
2. Ctrl + ;: Insert Current Date (Static timestamp)
3. Ctrl + Shift + :: Insert Current Time (Static timestamp)
4. Ctrl + D: Fill Down (Copies top cell into selected cells below)
5. Ctrl + R: Fill Right (Copies leftmost cell into selected cells to the right)
6. Ctrl + K: Insert Hyperlink (Adds a link to a cell)
7. Ctrl + Z: Undo (Reverses the last action)
8. Ctrl + Y: Redo (Repeats the last undone action)

Formatting (The Accountant’s Toolkit)

1. Ctrl + 1: Format Cells (Opens the full formatting menu)
2. Ctrl + Shift + %: Apply Percentage Format (0.00%)
3. Ctrl + Shift + $: Apply Currency Format (Default currency)
4. Ctrl + Shift + #: Apply Date Format (DD-MMM-YY)
5. Ctrl + Shift + !: Apply Number Format (Two decimals & thousands separator)
6. Alt + H + M + C: Merge & Center (Combines selected cells)
7. Alt + H + B: Add Border (Opens the border menu)

Formulas & Calculations

1. Alt + =: AutoSum (Inserts a SUM function automatically)
2. Shift + F3: Insert Function (Opens the function wizard)
3. F4: Toggle Absolute Reference (Adds $ to cell references in a formula)
4. Shift + F9: Calculate Active Sheet (Recalculates current worksheet only)
5. F9: Calculate All (Recalculates all open workbooks)

Data Analysis & Navigation

1. Ctrl + Shift + L: Toggle Filters (Adds/removes filter arrows)
2. Ctrl + T: Create Table (Converts range to an official Excel table)
3. Ctrl + F: Find (Searches for text or values)
4. Ctrl + H: Replace (Finds and replaces content)
5. Ctrl + Alt + V: Paste Special (Opens the Paste Special menu)
6. Alt + E + S + V: Paste Values (Quickly pastes values only, removing formulas)

Note: These are Windows shortcuts. Mac users will generally need to use Cmd instead of Ctrl, and Alt-key sequences may vary

• Ctrl + N - Create a new Power BI file

• Ctrl + O - Open an existing Power BI file

• Ctrl + S - Save the current report

• Ctrl + Shift + S - Save As

• Ctrl + P - Print report

• Ctrl + Z - Undo last action

• Ctrl + Y -Redo last action

• Ctrl + C -Copy selected visual/object

• Ctrl + V - Paste copied visual/object

• Ctrl + X - Cut selected item

• Ctrl + A - Select all elements

• Ctrl + F - Find/Search in report

• Ctrl + Shift + F -Open filter pane search

• Ctrl + Click -Multi-select visuals

• Delete - Remove selected visual/object

• Esc - Cancel current action

• F1 - Open Power BI help

• F2 - Rename selected field/object

• F5 - Refresh report

• Ctrl + R - Refresh visuals/data

• Alt + Shift + F10 - Open context menu

• Shift + Arrow Keys - Select multiple items

• Tab - Move to next object

• Shift + Tab - Move to previous object

• Arrow Keys -Navigate between visuals

• Alt + Shift + Up Arrow - Move visual up

• Alt + Shift + Down Arrow -Move visual down

• Alt + Shift + Left Arrow - Move visual left

• Alt + Shift + Right Arrow - Move visual right

• Ctrl + Mouse Wheel - Zoom in/out on report canvas

This comes up constantly. Someone gets handed a resource tracker or a system export where tasks are split across "Morning Task" and "Afternoon Task" columns, and they need a flat list to dump into a Pivot Table. Simple enough ask.

The fun part? Half these exports don't even leave cells blank - they write out [empty] as literal text, so any trick that relies on detecting blank cells just falls flat. And on top of that there's usually a Status column you need to drag along, but only once per person - not stamped next to every single task row like a broken rubber stamp.

Old solution was some deeply cursed nested INDEX/ROW formula that nobody could read six months later. If you're still doing that, please stop.

On Office 365 you can handle the whole thing in one shot:

=LET(data, A2:C11, status, D2:D11, col_data, TOCOL(data), col_status, TOCOL(IF(SEQUENCE(1,COLUMNS(data))=1, status, "")), FILTER(HSTACK(col_data, col_status), col_data<>"[empty]"))

TOCOL flattens the grid, the IF/SEQUENCE combo makes sure the status only shows up next to the name and not repeated under every task, HSTACK glues the two columns together, and FILTER kills all the [empty] noise. Keep your ranges the same size throughout or you'll get a #VALUE! staring back at you.

These dynamic array functions honestly flew under the radar for a lot of people who don't spend their weekends reading Excel update logs. Hope it saves someone a headache.

Note; for Excel 2019 and 2021 you can use power query. However in the 2021 version you can use the filter and sequence function.

In 2021 version; the formulas are;

=FILTER(INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]")

and

=FILTER(IF(INT((SEQUENCE(30)-1)/10)+1=1,INDEX(D2:D11,MOD(SEQUENCE(30)-1,10)+1),""),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]")

one thank go at user for pointing it out to me Excel_User_1977

News that I have been waiting for for a long time for notebooks. :)

Hi, do any of you use Norton 360 Deluxe VPN servers? I am very disappointed because they are too slow and few. Does this also happen to you who are slow in connecting? Who can you complain to? Thanks in advance.

1 Turn off everything but the essentials in VBA;

The below code sample shows you how to enable/disable:

• Manual calculations
• Screen updates
• Animations

​

Option Explicit

Dim lCalcSave As Long
Dim bScreenUpdate As Boolean

Sub SwitchOff(bSwitchOff As Boolean)
    Dim ws As Worksheet

    With Application
        If bSwitchOff Then
            ' OFF
            lCalcSave = .Calculation
            bScreenUpdate = .ScreenUpdating
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableAnimations = False

            For Each ws In ActiveWorkbook.Worksheets
                ws.DisplayPageBreaks = False
            Next ws
        Else
            ' ON
            If .Calculation <> lCalcSave And lCalcSave <> 0 Then
                .Calculation = lCalcSave
            End If
            .ScreenUpdating = bScreenUpdate
            .EnableAnimations = True
        End If
    End With
End Sub

Sub Main()
    SwitchOff True
    MyFunction
    SwitchOff False
End Sub

2. Disabling Office animations through system settings;

Animations can be disabled across Windows by accessing the Ease of Access Center.

Animations can be disabled in Excel specifically, under the Advanced or Ease of Access tab, within the File > Options menu.

Please see the following link for more information: https://support.office.com/en-us/article/turn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa025a

3 Disabling Office animations through registry settings

Office animations can be disabled across multiple computers by setting the appropriate registry key via a group policy setting. 

HIVE: HKEY_CURRENT_USER
Key Path: Software\Microsoft\Office\16.0\Common\Graphics
Key Name: DisableAnimations
Value type: REG_DWORD
Value data: 0x00000001 (1)

4 Removing unnecessary selects

The select method is common to see in VBA code, however it is often added to the macro where it isn’t needed. Select can trigger cell events such as animations and conditional formatting which slow the macro down, so removing unnecessary selects can significantly speed up your macro.

The following example shows the code before and after making the change to remove unnecessary selects.

Before

Sheets("Order Details").Select
Columns("AC:AH").Select
Selection.ClearContents

After

Sheets("Order Details").Columns("AC:AH").ClearContents

 4 Removing unnecessary selects

The select method is common to see in VBA code, however it is often
added to the macro where it isn’t needed. Select can trigger cell events
such as animations and conditional formatting which slow the macro
down, so removing unnecessary selects can significantly speed up your
macro.
The following example shows the code before and after making the change to remove unnecessary selects.

 Before
Sheets("Order Details").Select
Columns("AC:AH").Select
Selection.ClearContents

After
Sheets("Order Details").Columns("AC:AH").ClearContents

5 Using the With statement to read object properties

When working with objects, use the With statement to reduce the number of times object properties are read. The following example shows the code before and after making the change to use the With statement.

 

Before

Range("A1").Value = "Hello"
Range("A1").Font.Name = "Calibri"
Range("A1").Font.Bold = True
Range("A1").HorizontalAlignment = xlCenter

After

With Range("A1")
  .Value2 = "Hello" 
  .HorizontalAlignment = xlCenter
    With .Font
      .Name = "Calibri"
      .Bold = True
    End With
End With

6 Using ranges and arrays

Reading and writing to cells in Excel from VBA is expensive. There is an overhead that is incurred every time data moves between VBA and Excel.

This means that you should try to reduce the number of times you pass data between VBA and Excel. This is where ranges are useful. Instead of reading and writing to each cell individually in a loop, read the entire range into an array at the start, loop through the array, and then write the entire array back at the end. The following example code shows how a range can be used to read and write the values once, instead of reading each cell individually.

Dim vArray As Variant
Dim iRow As Long
Dim iCol As Long
Dim dValue As Double

vArray = Range("A1:C10000").Value2

For iRow = LBound(vArray, 1) To UBound(vArray, 1)
    For iCol = LBound(vArray, 2) To UBound(vArray, 2)
        If IsNumeric(vArray(iRow, iCol)) Then
            dValue = CDbl(vArray(iRow, iCol))
            If dValue > 0 Then
                dValue = dValue * dValue
            End If
            vArray(iRow, iCol) = dValue
        End If
    Next iCol
Next iRow

Range("A1:C10000").Value2 = vArray

7 Use .Value2 instead of .Text or .Value

There are different ways that you can retrieve values from a cell, and which property you use can make a different in the performance of your code.

.Text is commonly used to retrieve the value of a cell – it returns the formatted value of a cell. Getting the formatting of a cell is more complex than just retrieving a value, and makes .Text quite slow.

.Value is an improvement over .Text, as this mostly gets the value from the cell, without formatting. However for cells formatted as a date or currency, .Value will return a VBA date or VBA currency (which may truncate decimal places).

.Value2 gives the underlying value of the cell. As it involves no formatting, .Value2 is faster than .Value. .Value2 is faster than .Value when processing numbers (there is no significant difference with text), and is much faster using a variant array.

8 Bypass the clipboard (copy and paste)

When you use the Macro Recorder to record operations that use copy and paste, the code will use the copy and paste methods by default. However, within VBA code, it is much faster to bypass the clipboard and use internal operations instead. By default, copying will copy everything, including formulas, values and formatting. You can make copying faster by only copying values or formulas, without the formatting. The following example shows the code before and after making the change to bypass the clipboard.

Before

Range("A1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste

After

' Approach 1: copy everything (formulas, values and formatting
Range("A1").Copy Destination:=Range("A2")

' Approach 2: copy values only
Range("A2").Value2 = Range("A1").Value2

'Approach 3: copy formulas only
Range("A2").Formula = Range("A1")

9. Use Option Explicit to catch undeclared variables

Option Explicit is one of the available Module directives in VBA that instructs VBA how to treat the code within the code module. Setting Option Explicit requires all variables to be declared and will give compile errors if an undeclared variable is used. This helps catch incorrectly typed variable names and improves performance with all variable types being defined at compile time, instead of being inferred at runtime.

This can be set by typing: Option Explicit at the top of each module in your project or by checking the "Require Variable Declaration" option under Tools -> Options in the VBA editor. 

https://thecyberwire.com/podcasts/hacking-humans/385/notes

Not trying to start a war, but I wasted years overusing VLOOKUP for stuff it’s just bad at.

A B C

ID Name Score

101 Anna 88

102 Luca 92

103 Marco 75

Most people do this:

=VLOOKUP(102, A2:C4, 3, FALSE)

Looks fine… until:

you insert a column > formula breaks

you need to look left >can’t

you forget what "3" even means

What finally fixed it for me:

=INDEX(C2:C4, MATCH(102, A2:A4, 0))

Same result, but:

no random column numbers

doesn’t break when you touch the table

actually scales when your file gets messy

The moment it clicked was realizing: MATCH finds the row, INDEX returns the value. That’s it.

Also, if you’re on newer Excel, just use:

=XLOOKUP(102, A2:A4, C2:C4)

Way cleaner.

Not saying VLOOKUP is useless, but if you’re still defaulting to it for everything, you’re probably making your spreadsheets more fragile than they need to be.

Stop using For Each Cell In Range on big datasets. It's too slow and will just hang Excel and kill the UI. If you're looping more than a few hundred rows this way, you're doing it wrong. It's basic.

Just dump the whole range into an array in RAM. Crunch the data in memory. Then 'spray' it back to the sheet in one shot. It's much faster.

Sub FastCleanData()
    Dim data As Variant
    Dim i As Long
    
    ' Pull everything into RAM
    data = [A1:B10000].Value 
    
    For i = 1 To UBound(data)
        ' Process logic in RAM
        data(i, 1) = Trim(UCase(data(i, 1)))
    Next i
    
    ' Push back to sheet
    [A1:B10000].Value = data
End Sub

The reason it's slow is the VBA-to-Sheet communication. That's the bottleneck. One single read followed by one single write is almost instant. 10,000 separate reads and writes will make Excel hang every time.

Keyboard-only move: Don't reach for the mouse to run this. Alt+F8 > type name > Enter