GUI system won't work?

Classic error. You're changing StarterGui and not PlayerGui

local Leaderboard = game.Players.LocalPlayer:WaitForChild("leaderstats")
local CeleGui = game.Players.LocalPlayer:WaitForChild("PlayerGui"):WaitForChild("Celebrate") --Defines the player's current GUI instead of the StarterGUI.

CeleGui.Frame.Visible = false

function celebrate()
    local player = game.Players.LocalPlayer
    CeleGui.Frame.Visible = true
    CeleGui.Frame.TextBox.Text = ""..player.Name.." has ranked up to "..Leaderboard.Rank.Value.."!"
    wait(3)
    CeleGui.Frame.Visible = false
end

Leaderboard.RankVal.Changed:connect(function(value)
    if value == 1 then Leaderboard.Rank.Value = "Newbie" elseif
        value == 2 then Leaderboard.Rank.Value = "Member" elseif
        value == 3 then Leaderboard.Rank.Value = "Forager" elseif
        value == 4 then Leaderboard.Rank.Value = "Watcher" elseif
        value == 5 then Leaderboard.Rank.Value = "Night Watcher" elseif
        value == 6 then Leaderboard.Rank.Value = "Warrior" elseif
        value == 7 then Leaderboard.Rank.Value = "Runner" elseif
        value == 8 then Leaderboard.Rank.Value = "Elder"
        celebrate()
end
end)

Leaderboard.RankVal.Changed:connect(function(value)
    celebrate()
end)

Explanation

Changing StarterGuiwill only affect the Player's Gui after they respawn.But changing the PlayerGui changes it in real time.